Update Vendor Sites through API

/* Formatted on 6/11/2018 10:23:39 AM (QP5 v5.287) */
DECLARE
   p_api_version                 NUMBER;
   p_init_msg_list               VARCHAR2 (200);
   p_commit                      VARCHAR2 (200);
   p_validation_level            NUMBER;
   x_return_status               VARCHAR2 (200);
   x_msg_count                   NUMBER;
   x_msg_data                    VARCHAR2 (200);
   lr_vendor_site_rec            apps.ap_vendor_pub_pkg.r_vendor_site_rec_type;
   lr_existing_vendor_site_rec   ap_supplier_sites_all%ROWTYPE;
   p_vendor_site_id              NUMBER;
   p_calling_prog                VARCHAR2 (200);
BEGIN
   -- Initialize apps session

   fnd_global.apps_initialize (1119, 50833, 200);

   mo_global.init ('SQLAP');

   fnd_client_info.set_org_context (101);



   -- Assign Basic Values
   p_api_version := 1.0;
   p_init_msg_list := fnd_api.g_true;
   p_commit := fnd_api.g_true;
   p_validation_level := fnd_api.g_valid_level_full;
   p_vendor_site_id := 3918;                                -- to be end dated
   p_calling_prog := 'XXBSDA';

   BEGIN
      SELECT *
        INTO lr_existing_vendor_site_rec
        FROM ap_supplier_sites_all assa
       WHERE assa.vendor_site_id = p_vendor_site_id;
   EXCEPTION
      WHEN OTHERS
      THEN
         DBMS_OUTPUT.put_line (
               'Unable to derive the supplier site information for site id:'
            || p_vendor_site_id);
   END;



   -- Assign Vendor Site Details

   lr_vendor_site_rec.vendor_site_id   := lr_existing_vendor_site_rec.vendor_site_id;
 
   lr_vendor_site_rec.last_update_date := SYSDATE;
   --
   lr_vendor_site_rec.last_updated_by  := 52741;
   lr_vendor_site_rec.HOLD_ALL_PAYMENTS_FLAG := 'Y';



   AP_VENDOR_PUB_PKG.UPDATE_VENDOR_SITE (
      p_api_version        => p_api_version,
      p_init_msg_list      => p_init_msg_list,
      p_commit             => p_commit,
      p_validation_level   => p_validation_level,
      x_return_status      => x_return_status,
      x_msg_count          => x_msg_count,
      x_msg_data           => x_msg_data,
      p_vendor_site_rec    => lr_vendor_site_rec,
      p_vendor_site_id     => p_vendor_site_id,
      p_calling_prog       => p_calling_prog);



   DBMS_OUTPUT.put_line ('X_RETURN_STATUS = ' || x_return_status);
   DBMS_OUTPUT.put_line ('X_MSG_COUNT = ' || x_msg_count);
   DBMS_OUTPUT.put_line ('X_MSG_DATA = ' || x_msg_data);
END;
/

SQL - AR Customer - Bill to site addresses

SELECT
party.party_name "Customer Name",

loc.ADDRESS1
|| ' '

|| loc.ADDRESS2

|| ' '

|| loc.ADDRESS3

|| ' '

|| loc.POSTAL_CODE

|| ' '

|| loc.CITY

|| ' '

|| NVL (loc.STATE, loc.PROVINCE)

|| ' '

|| loc.COUNTRY

|| ' '

|| ship.location

"Bill to address "

FROM apps.hz_cust_accounts cust,

apps.hz_cust_acct_sites_all acct,

apps.hz_cust_site_uses_all ship,

apps.hz_party_sites party_site,

apps.hz_locations loc,


apps.hz_parties party
WHERE cust.cust_account_id = acct.cust_account_id

AND acct.cust_acct_site_id = ship.cust_acct_site_id

AND acct.ORG_ID = ship.ORG_ID

AND ship.SITE_USE_CODE = 'BILL_TO'

AND cust.status = 'A'

AND loc.location_id = party_site.location_id

AND acct.party_site_id = party_site.party_site_id

AND cust.party_id = party.party_id

AND party.party_name LIKE '96%'

ORDER BY 1

Debug Customer Statement Generation

“Customer Statement Generation” Program has its own pros and cons. Some client implement it and other ignored it but it is one of good features to use. Many time we get performance or data issues and need to troubleshoot, here are some step to identify any problems :

Enable “Debug Flag” at program level

Responsibility: System Administrator
Navigation: Concurrent > Program > Define
Query shortname = ARXSGP
Click Parameters
Scroll to parameter debug_flag

This might be the last parameter, seeded value for default = select meaning from  fnd_lookups where lookup_type = ‘YES_NO’ and lookup_code = ‘N’

Change this to : select meaning from fnd_lookups where lookup_type = YES_NO’ and lookup_code = ‘Y’  and Save

Set Profile Option ( AR: Enable Debug Message Output ) as Yes

Responsibility: System Administrator
Navigation: Profile > System

Query for Application = Receivables
user =
Profile Option = AR: Enable Debug Message Output – Ensure it is set to Yes.

Normally, the statement generation process creates data in the AR_STATEMENT* tables which are used for statement printing. After the statements process completes, it deletes data in these temporary tables. When you need to debug the behavior of statements, it is sometimes necessary to see the data that was populated into these tables. You can disable the deletion of records in the tables by doing the following.

Responsibility: Receivables Manager
Navigation: Print Documents > Statements

Invoke the Menu: Help > Diagnostic > Examine  –Enter the following values:

Block = SUPST_BLK
Field = DEBUG_FLAG
Value = Y
Click OK and Submit the program

Verfiy Log file :  Review the log file generated by the Concurrent Process: Statement Generation Program. If debug was enabled correctly, you should see text like the following in the log file.

• arxsgp: Argument 38 Y
• arxsgp: AR_ENABLE_DEBUG_OUTPUT = Y
• arxsgp: DEBUG_FLAG is Y, records in interim tables will NOT be deleted

The Statements program use the following 2 interim tables to generate the statement:
The AR_STATEMENT_HEADERS and AR_STATEMENT_LINE_CLUSTERS are interim tables populated by the Statement Generation program. The code creates rows in these tables that are then picked up by the Print Statements program

SQL - Assign Responsibility

--assign resp
--Find the RESPONSIBILITY_ID from RESPONSIBILITY_NAME:

select RESPONSIBILITY_ID,RESPONSIBILITY_NAME from FND_RESPONSIBILITY_VL where RESPONSIBILITY_NAME like 'Sys%Admin%';

--Verify whether the User is already having the responsibility assigned:
select USER_NAME,USER_ID from fnd_user where USER_NAME = 'JXGUPTA';
select USER_ID,RESPONSIBILITY_ID from FND_USER_RESP_GROUPS_DIRECT where USER_ID=52741 and RESPONSIBILITY_ID=20420;

--Assign the Responsibility Using the Following SQL:
;
DECLARE

  v_user_name  VARCHAR2(30) := 'JXGUPTA';
  Enter_Resp_Id  VARCHAR2(100) ;
  v_resp_appl  VARCHAR2(100);
  v_resp_key   VARCHAR2(100);
  v_appl_id    VARCHAR2(30);

BEGIN
select APPLICATION_ID ,RESPONSIBILITY_KEY
  into v_appl_id ,v_resp_key
  from FND_RESPONSIBILITY_VL
where RESPONSIBILITY_ID=20420 ;

select APPLICATION_SHORT_NAME
  into v_resp_appl
  from FND_APPLICATION_VL  
where upper(APPLICATION_ID) = v_appl_id;

  fnd_user_pkg.addresp(username       => v_user_name
                      ,resp_app       => v_resp_appl
                      ,resp_key       => v_resp_key
                      ,security_group => 'STANDARD'
                      ,description    => 'Auto Assignment'
                      ,start_date     => SYSDATE
                      ,end_date       => SYSDATE + 1000);
END;

SQL- reset password

you can use one of the 2 ways mentioned below :

1. select fnd_web_sec.change_password ('SCOTT', 'oracle99')  from dual;


2.
begin
apps.fnd_user_pkg.updateuser(
x_user_name => 'SCOTT'
, x_owner => 'SEED'
, x_unencrypted_password => 'welcome001'
, x_password_date => to_date('2','J'));
commit;
end;
/

HRMS wages query

SELECT papf.employee_number 
,papf.full_name 
,pj.NAME job 
,haou.NAME ORGANIZATION 
,ppp.proposed_salary_n salary 
FROM per_all_people_f papf 
,per_all_assignments_f paaf 
,per_jobs pj 
,hr_all_organization_units haou 
,per_position_definitions ppd 
,per_all_positions pap 
,per_pay_proposals ppp 
WHERE 1 = 1 
AND SYSDATE BETWEEN papf.effective_start_date AND papf.effective_end_date 
AND papf.current_employee_flag = 'Y' 
AND papf.employee_number IS NOT NULL 
AND paaf.person_id = papf.person_id 
AND SYSDATE BETWEEN paaf.effective_start_date AND paaf.effective_end_date 
AND paaf.job_id = pj.job_id 
AND paaf.organization_id = haou.organization_id 
AND paaf.position_id = pap.position_id 
AND pap.position_definition_id = ppd.position_definition_id 
AND ppp.pay_proposal_id = (SELECT MAX (pay_proposal_id) 
                                                  FROM per_pay_proposals 
                                               WHERE assignment_id = paaf.assignment_id)

                          

Viewing Output/Log in Browser

Reports that produce an output other than a text file (for example HTML or PDF) are displayed in a browser. If you set the profile option VIEWER: TEXT to Browser, your text files are also displayed in a browser as opposed to the Report Viewer. VIEWER: TEXT may also be set to display your output in a text editor such as Microsoft Word, or in a spreadsheet such as Microsoft Excel.

You can use the Save As functionality of your browser to save an HTML report to your desktop. If your system supports multiple languages, you may have trouble with the saved output. Ask your system administrator for assistance with character set conversion from the server to your browser.

The name of the output/report file passed to the browser is created dynamically. The concurrent program name and the date on which the request was run are used to create the name of the file. The file extension is based on the Output Type" attribute of the concurrent program.

For example:

Request 1234 for the "Year End Exception Report" is run on September 27, 2001 and has an output file named o1234.out.

When you view this report online, 'Year End Exception Report_270901.html' is passed as the name of the file to the browser in the content header in addition to the mime-type. You can then save the file locally using this name.