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.

How to Use E-Signature Support For PO Receipt (Doc ID 864598.1)

How to Use E-Signature Support For PO Receipt (Doc ID 864598.1)

here is not a standard approval workflow process for receiving items. Core 
Receiving does not have such functionality, it only checks that the originator 
of the document is valid.

You may nevertheless use the ERES E-Records and E-Signatures functionality 
in Purchasing (ie. Inspection, Transfer, Deliver) but you must implement Oracle 
Quality and set the profile option QA:PO Inspection to Oracle Quality in order 
to capture E-Records and E-Signatures in Oracle Purchasing. iSignatures enables 
users to upload any type of file to the database and to route it for approval using 
the ERES framework.

The only E-Record Enabled Events associated with Workflow Seeded Data 
will be: 

• Inspect Received Items (Receiving Quality Inspection)
• Transfer (Receiving Transfer with Quality Data Collection)
• Deliver to Inventory (Receiving Delivery with Quality Data Collection)

For more information, please refer to the guides below:

1. Implementing Oracle E-Records in Discrete Manufacturing Guide
a. Introduction>> Discrete Manufacturing Business Flows Using Oracle 
    E-Records>> Procure to Pay
b. Oracle E-Records Enabled Transactions Summary>> Oracle Purchasing

2. Oracle Quality User's Guide
a. E-records and E-Signatures for Oracle Purchasing

The following notes can be followed for more details on the setup:
Note 336709.1 Implementing E-Records and E-Signatures (11.5.10) in Discrete Manufacturing Suite
Note 864598.1 How to Use E-Signature Support For PO Receipt

Ref BUG:8654554 - ONDEMAND: ERECORD AND ESIGNATURE NOT WORK DURING DELIVER

Status of Closed on a task populate an end date automatically

    CURSOR c_task_details IS
      SELECT jt.task_id,
             jt.task_number,
             jt.object_version_number,
             jt.task_status_id,
             jt.owner_id,
             jt.customer_id,
             jt.last_update_date
      FROM jtf_tasks_b jt,
           jtf_task_statuses_tl ts
      WHERE jt.task_status_id = ts.task_status_id
      AND   ts.name = 'Closed'
      AND   jt.scheduled_end_date IS NULL
      AND   TRUNC(jt.last_update_date) = TRUNC(SYSDATE);
    --
  BEGIN
    --For Each Task
    FOR rec_task_details IN c_task_details
    LOOP
      x_total_count := x_total_count + 1;
      x_api_call_status := 'S';
      x_error_message   := NULL;
      x_temp_str        := NULL;
      --
      fnd_msg_pub.Delete_Msg;
      --
      --Call API to Update Schedule End Date to Sysdate for Today's Closed Task
      BEGIN
        jtf_tasks_pub.update_task(p_api_version => 1.0,
                                  p_object_version_number  => rec_task_details.object_version_number,
                                  p_task_id                => rec_task_details.task_id,
                                  p_task_number            => rec_task_details.task_number,
                                  p_task_status_id         => rec_task_details.task_status_id,
                                  p_owner_id               => rec_task_details.owner_id,
                                  p_customer_id            => rec_task_details.customer_id,
                                  p_scheduled_end_date     => rec_task_details.last_update_date,  --Updatable
                                  x_return_status          => x_o_return_status,
                                  x_msg_count              => x_o_msg_count,
                                  x_msg_data               => x_o_msg_data
                                  );

      EXCEPTION
        WHEN others THEN
          x_api_call_status := 'F';
          x_failure_count := x_failure_count + 1;
          x_error_message := '(Task Number = '||rec_task_details.task_number||')Unknown Error while getting Task Details : '||SUBSTR(SQLERRM,1,100);
          fnd_file.put_line(fnd_file.log, x_error_message);
          xxcnc_utilities_pkg.xxcnc_insert_error(rec_task_details.task_id,
                                                 'Calling API',
                                                 'JTF_TASKS_B',
                                                 'TASK_NUMBER',
                                                 x_error_message);
      END;
      --
      IF x_api_call_status <> 'F' THEN
        --Check Status
        IF x_o_return_status <> FND_API.G_RET_STS_SUCCESS THEN
          x_failure_count := x_failure_count + 1;
          --
          x_o_msg_count           := FND_MSG_PUB.COUNT_MSG;
          --Check Message Count
          IF x_o_msg_count > 0 THEN
            x_temp_str      := SUBSTR(FND_MSG_PUB.GET(FND_MSG_PUB.G_FIRST,FND_API.G_FALSE),1,512);
            x_error_message := x_temp_str;
            --
            FOR i in 1..(x_o_msg_count -1)
            LOOP
              x_temp_str      := SUBSTR(FND_MSG_PUB.GET(FND_MSG_PUB.G_NEXT, FND_API.G_FALSE),1,512);
              x_error_message := x_error_message||' '||x_temp_str;
            END LOOP;
          END IF;
          x_error_message := '(Task Number = '||rec_task_details.task_number||')'||x_error_message;
          fnd_file.put_line(fnd_file.log, x_error_message);
          --Log appropriate Error Message
          xxcnc_utilities_pkg.xxcnc_insert_error(rec_task_details.task_id,
                                                 'Return Status = F',
                                                 'JTF_TASKS_B',
                                                 'TASK_NUMBER',
                                                 x_error_message);
          --
        ELSE  --Success
          x_success_count := x_success_count + 1;
        END IF;
      END IF;
      --
    END LOOP;
    COMMIT;