Adding responsibility to the user using API

 DECLARE

lv_user_name VARCHAR2 (20) := 'ABC.PQR';

lv_req_resp_key VARCHAR2 (50) := 'SYS-ADMIN';

lv_description VARCHAR2 (100) := 'Adding Responsibility to user using script';

lv_req_resp_name VARCHAR2 (200);

lv_appl_shrt_name VARCHAR2 (20);

lv_appl_name VARCHAR2 (50);

lv_resp_key VARCHAR2 (50);

BEGIN

SELECT fav.application_short_name,

fav.application_name,

frv.responsibility_name

INTO lv_appl_shrt_name, lv_appl_name, lv_req_resp_name

FROM fnd_application_vl fav, fnd_responsibility_vl frv

WHERE frv.application_id = fav.application_id

AND frv.responsibility_key = lv_req_resp_key;

fnd_user_pkg.addresp (username => lv_user_name,

resp_app => lv_appl_shrt_name,

resp_key => lv_req_resp_key,

security_group => 'STANDARD',

description => lv_description,

start_date => SYSDATE,

end_date => NULL

);

COMMIT;

DBMS_OUTPUT.put_line ('The responsibility ' || lv_req_resp_name || ' is added to the user ' || lv_user_name);

EXCEPTION

WHEN OTHERS THEN

DBMS_OUTPUT.put_line ('Responsibility IS NOT added due to ' || SQLCODE || '; ' || SUBSTR (SQLERRM, 1, 250));

ROLLBACK;

END;

Program status check

 SELECT

    request_id,

    decode(status_code, 'A', 'Waiting', 'B', 'Resuming',

           'C', 'Normal', 'D', 'Cancelled', 'E',

           'Error', 'F', 'Scheduled', 'G', 'Warning',

           'H', 'On Hold', 'I', 'Normal', 'M',

           'No Manager', 'Q', 'Standby', 'R', 'Normal',

           'S', 'Suspended', 'T', 'Terminating', 'U',

           'Disabled', 'W', 'Paused', 'X', 'Terminated',

           'Z', 'Waiting')                                    status,

    decode(phase_code, 'C', 'Completed', 'I', 'Inactive',

           'P', 'Pending', 'R', 'Running', 'Others')          phase,

    to_char(request_date, 'DD-MON-YYYY HH24:MI:SS')           request_date,

    requestor,

    to_char(actual_start_date, 'DD-MON-YYYY HH24:MI:SS')      actual_start_date,

    to_char(actual_completion_date, 'DD-MON-YYYY HH24:MI:SS') actual_completion_date,

    to_char(round((actual_completion_date - actual_start_date) * 1440, 0))

    || ' Mins '

    || to_char(round(mod((actual_completion_date - actual_start_date) * 86400, 60),

                     2))

    || ' Secs'                                                duration,

    argument_text,

    rtl.responsibility_name,

    program,

    program_short_name,

    rtl.responsibility_id,

    concurrent_program_id,

    priority_request_id,

    priority,

    printer,

    print_style,

    user_print_style,

    requested_start_date

FROM

    apps.fnd_conc_req_summary_v conc,

    apps.fnd_responsibility_tl  rtl

WHERE

        conc.responsibility_id = rtl.responsibility_id

--and status_code = 'E'

    AND language = 'US'

    AND program LIKE '%OPQR%'

    AND request_date > sysdate - 9

ORDER BY

    request_date DESC;

Pricing details for audit purpose

 SELECT distinct

    pi.list_header_id,

    pl.name AS price_list_name,

    pil.list_line_id,

    pil.INVENTORY_ITEM_ID,

    msib.segment1 AS item_code,

    msib.description AS item_description,

    --pil.unit_price,

    pil.start_date_active,

    pil.end_date_active

    --pil.currency_code

    , (select user_name from fnd_user  where user_id = pil.CREATED_BY ) created_by_user

    , pil.CREATION_DATE creation_date

    , (select user_name from fnd_user  where user_id = pil.LAST_UPDATED_BY ) updated_by_user

    , pil.LAST_UPDATE_DATE updated_date

FROM  apps.qp_list_headers_all pi

    , qp_list_lines pil

    , apps.mtl_system_items_b msib 

    , apps.qp_list_headers_tl pl

WHERE  msib.organization_id = 9786 --:ORG_ID

    and pi.list_header_id = pil.list_header_id

    and pi.list_header_id = 1234

    and pil.INVENTORY_ITEM_ID = msib.inventory_item_id

    and pi.list_header_id = pl.list_header_id

    AND msib.segment1 ='12345-6'

  --  AND pil.start_date_active <= SYSDATE

   -- AND (pil.end_date_active IS NULL OR pil.end_date_active >= SYSDATE)

ORDER BY

    pil.start_date_active DESC;

PO Queries

 

/** PO Data**/

select * from PO_HEADERS_ALL order by po_header_id desc;

select * from PO_LINES_ALL;

select * from PO_DISTRIBUTIONS_ALL;

select *
from po_headers_all pha,
     po_lines_all pla,
     po_distributions_all pda
where pha.po_header_id = pla.po_header_id
and pla.po_header_id = pda.po_header_id
and pla.po_line_id = pda.po_line_id;      

select * from PO_LINE_LOCATIONS_ALL;

/**Requisition**/
select * from PO_REQUISITION_HEADERS_ALL;

select * from PO_REQUISITION_LINES_ALL;

select * from PO_REQ_DISTRIBUTIONS_ALL;

/**AP Data**/
select * from ap_suppliers;

select * from ap_supplier_sites_all;
   
select * from ap_supplier_contacts;

select * from PO_RELEASES_ALL;

select * from mtl_parameters where organization_code = 'SS1';

/**Receiving**/
select * from RCV_SHIPMENT_HEADERS where receipt_num = '7426' and ship_to_org_id = 414;

select * from RCV_SHIPMENT_LINES where shipment_header_id = 1953439;

select * from RCV_TRANSACTIONS where shipment_header_id = 1953439;

SELECT *
  FROM rcv_shipment_headers       rsh,
       rcv_shipment_lines         rsl,
       rcv_transactions           rt,
       mtl_material_transactions  mtt
 WHERE     rsh.shipment_header_id = rsl.shipment_header_id
       AND rsl.shipment_header_id = rt.shipment_header_id
       AND rsl.SHIPMENT_LINE_ID = rt.SHIPMENT_LINE_ID
       AND rt.transaction_id = mtt.RCV_TRANSACTION_ID
      -- AND rt.transaction_type = 'DELIVER'
       and rsl.po_header_id in (2053065, 2176946);
 

--Routing:
--a) Direct Delivery - PO received and delivered in single step
--b) Standard Receipt - First PO is received in receiving bay and then delivered to subinventory.
--c) Inspection Required - Here Po is first received ,then inspection is done to check quality of item and then delivered

-- PO item in the receiving bay and then delivering to subinventory via receiving transaction processor (Triggered at receive and delivery)

select * from rcv_shipment_headers;

select * from rcv_shipment_lines;

select * from rcv_transactions; -- PO Delivery Transaction details are captured

select distinct(auto_transact_code) from rcv_transactions_interface;  -- DELIVER

select * from mtl_material_transactions; -- inventory table talks about the On-hand increment when PO is delivered to specific subinventory

SOP: Level 2 Support General Guidelines

First 60 mins ( Quick check)

1.Email or teams group chat check for any priority issue ( P1/P2) handover

2.Email check for client meetings, internal meetings , escalation from client in case any.

3.Check queue for your Track (Technical) if any P1/P2 active, start working immediately if there is any.

4.Check your queue for Active tickets

5.Check for your queue from oldest to newest tickets based on creation date

Remaining of the day

1.Is there any P1/P2 incident open, any newly added incidents needs to be checked every Hour

2.Star working on “Active” , “Awaiting End User", "Pending 3rd Party”.

3.First in first out approach to reduce aging incidents.

4.Check if the ticket is updated for correct values for Configuration, Category, Sub- category, Configuration item , Status etc. for each incident.

5.Continue working on incidents/meeting/change creation/ user follow ups etc.

6.Check and plan for meeting for next day. Prepare for analysis 

7.Keep track on any new incident that gets added.

Last 60 mins (Before log out…….. Check below )

1.Notify pears (support DL) for any priority issue you could be handling.

2.Handover email for any P1/P2 issues.

3. Ensure you are not missing SLA on any of the incidents in your queue.

3rd Paty issues

Any Inbound/outbound, integration related issues can be categories as Third Party issues.

Please raise a new incident (Spin off ticket) with the respective team and ensure to mention that number in the original short description of the incident. 

Dos

1.Every incidents need to be updated within 5 working days.

2.Allow 5 reminders in case of user not responding on any incident.

3.Create queue for monitoring your incidents and your track related incidents.

4.Every 60 min please check for any P1/P2 incident in case you find any notify the team on Teams chat , Send email subjecting the incident number, in case of weekend notify on Whats App group.

5.Adhere to shift timings as per plan.

6.Update Minutes of meeting on the incident to have visibility of what is discussed during the meeting.

In case of long leave ensure your incidents are updated and notify the team and update the Shift roaster for the same.

Out of Scope

Kindly mark any new business requirement, new process, new program/report for Subcategory= Enhancement as there exists a separate team for the same.

When to create a Problem ticket

1.For any repetitive issue observed but not able to reproduce on non-production environment.

Performance issues

2.Complex issues which needs interaction between multiple teams.

What is CAB readiness for Change Management

1.Change State need to be in ‘’Prod Review”

2.Change description should explain the reason for the change creation do not copy text from incident while creating change.

3.Link incident to the change created.

4.Check if RICE or Instruction document attached

5.Check if user approval email attached for QA/Test 

6.Is test plan updated ?

7.Is backout plan updated ?

8.Is code review complete ?

9.For customer facing documents like (OA, Invoices, Pos) is Regression testing complete?

10. If Yes is Testing results reviewed by business owners? Are they approved?

11.In case of DATAFIX is the datafix_document template attached? For Oracle SRs is the reference mentioned.

12.In case of any manual setup or steps is access requested in PTRXI or it is part of the instruction document attached.

Supplier details - data extract

 SELECT

    pv.vendor_id,

    pv.segment1 AS vendor_number,

    pv.vendor_name,

    pv.vendor_type_lookup_code,

    pv.enabled_flag,

    pv.start_date_active,

    pv.end_date_active,

    pvs.vendor_site_id,

    pvs.vendor_site_code,

    pvs.address_line1,

    pvs.address_line2,

    pvs.city,

    pvs.state,

    pvs.zip,

    pvs.country,

    pvs.phone,

    pvs.fax,

    pvs.email_address,

    pvs.creation_date

FROM 

    apps.ap_suppliers pv,

    apps.ap_supplier_sites_all pvs

WHERE 

    pv.vendor_id = pvs.vendor_id

    AND pv.enabled_flag = 'Y'

    AND pvs.inactive_date IS NULL

ORDER BY 

    pv.vendor_name, pvs.vendor_site_code;

Interview Questions and answers- PL SQL, Oracle Apps, Technical, Functional

 1. Cursors

PL/SQL allows the programmer to control the context area through the cursor. 

A cursor holds the rows returned by the SQL statement. The set of rows the cursor holds is referred as active set.

Cursor curser_name is (parameter1 datatype)

select e.name , c.salary

from employee e,

perks c,

where e.eid  = c.pid

and c.salary >= 1000 ;

===============================

1.1 implicit cursors

Curser used by system to complete DML (Delete, insert, update) transaction is using this type of cursor.

===============================

1.2 explicit cursors

this is mainly used to get required data set to execute further actions, using Select clause. This needs definition, call for opening and closing.

=================================

1.3 cursor attributes 

Cursor Attribute Description

%FOUND   -It returns the Boolean result 'TRUE' if the most recent fetch operation fetched a record successfully, else it will return FALSE.

%NOTFOUND -This works oppositely to %FOUND it will return 'TRUE' if the most recent fetch operation could not able to fetch any record.

%ISOPEN   - It returns Boolean result 'TRUE' if the given cursor is already opened, else it returns 'FALSE'

%ROWCOUNT -It returns the numerical value. It gives the actual count of records that got affected by the DML activity.

=================================

1.4 for loop cursor application

"FOR LOOP" statement can be used for working with cursors. We can give the cursor name instead of range limit in the FOR loop statement so that the loop will work from the first record of the cursor to the last record of the cursor. The cursor variable, opening of cursor, fetching and closing of the cursor will be done implicitly by the FOR loop.

===================================

1.5 Difference between static and dynamic cursors

1.6 Ref cursors

PL/SQL Cursor Variables with REF CURSOR (oracletutorial.com) 

1.7 dynamic sql

Oracle PL/SQL Dynamic SQL Tutorial: Execute Immediate & DBMS_SQL (guru99.com)

1. execute immediate

EXECUTE IMMEDIATE(<SQL>)
[INTO<variable>]
[USING <bind_variable_value>]

2. DBMS _sql

PL/SQL provide the DBMS_SQL package that allows you to work with dynamic SQL. The process of creating and executing the dynamic SQL contains the following process.
OPEN CURSOR: The dynamic SQL will execute in the same way as a cursor. So in order to execute the SQL statement, we must open the cursor.
PARSE SQL: The next step is to parse the dynamic SQL. This process will just check the syntax and keep the query ready to execute.
BIND VARIABLE Values: The next step is to assign the values for bind variables if any.
DEFINE COLUMN: The next step is to define the column using their relative positions in the select statement.
EXECUTE: The next step is to execute the parsed query.
FETCH VALUES: The next step is to fetch the executed values.
CLOSE CURSOR: Once the results are fetched, the cursor should be closed.

==================================================================

2. Triggers

==================================================================

3. Exceptions

Oracle PL/SQL Exception Handling: Examples to Raise User-defined Exception (guru99.com)

==================================================================

4. what is NO COPY

5. Nested tables

6. Pragma autonomous transactions and its application

7.  Global temporary tables and transactions

8. Views

9. Collections, Bulk collect

Oracle PL/SQL BULK COLLECT: FORALL Example (guru99.com)

10. NVL, NVL2

11. what is RICEW , explain  with each example

- Reports, Interfaces, Conversions, Extensions, Workflow

12. Reports, triggers in reports 

13. Forms, why do you need Custom.pll , how and where do you use it, give example

14. Form personalization steps, what is profile option for this

15. wht is profile options

16. what is flex fields, dff, kff its differences

17. how to use OAF, can we change value run time, development steps of OAF. what is OAF MVC architecture.

18. what is a Workflow, how to customize workflow, development steps

19. what is XML report, define steps to build the same

20.  XML publisher, xml parser, ZPL with example, discoverer reports

21. Mandatory parameters in API

22. Seeded APIs - create customers, create orders, create PO etc

23. what is tkprof

24. performance tunning with examples, how to tune given query

25. what is business events in oracle, how to write code for the same

26. what is hook, what are types of hooks. Difference between hooks and business events. where do you use hooks

27. what is processformdata, what is controller

28. how to add picture in report

29. how to change language for report or program specific to country. explain with example.

30. explain Order to Cash flow with table details.

Drop ship orders- 

Items which are mot produced but procured from supplier and fulfilled.

the main setup here is the Item setup --> OM tab having source type as EXTERNAL

OM : Create sales order --> Book the order --> status - booked

click on Progress order --> Purchase Release eligible --> click OK --> now status - Awaiting shipping

Go to purchasing resp --> run program 'Purchase Release' sales order number as parameter --> run

once complete then

run program 'Requisition Import ' where parameter import source - Order Import 

important link - oracle drop ship order flow (erpschools.com)

Order management -->  Shipped --> awaiting billing --> AR interface --> AR invoice -->amount recieved --> create reciept  --> complete

here system automatically places order on supplier 

( approved supplier list, sourcing rules, assigning to sourcing rules, sourcing rule assignment sets, blanket purchase agreement)

31. How do you define a new order type

32. explain orders - CTO, PTO, ATO, Drop ship with examples

33. explain P2P cycle with table details

Oracle Apps: P2P(Procure-to-Pay) Cycle Tables with Joins (kommaoracleapplications.blogspot.com)

34. what are types of purchase orders, requisition types

35. what is match recipt, which tables do you check for this

36. can we define item on warehouse without defining in master org? tables involved in item definition.

37. how to check on hand quantity, tables for the same

38. what is bursting ? its application

39. WMS what is it ? its application

40. difference between WMS and inventory systems

41. what are month end closure activities (list all)

42. what are 3 Cs , define all different types of calendars.

43. when do you need to run inventory interface, what does it do

44. how do you define request set, explain steps. Can we hide mandatory parameters

45. what is XML gateway, how to set it up, explain tables.

48. what is FTP, how do you use it, what file can you read.

49. tables for AP invoices, Recipts

50. Finance cycle and its tables, cogs accounting what is it.

51. how to find out a program is run though which responsibility, explain by writing query. 

Ans - 

52. How to set Organization Context in R12

mo_global.init ('AR');

mo_globa.set_policy_context ('S', '&org_id') ;