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;