Thursday, November 27, 2014

How to Get the XML for Check Printing in R12

In R12, Format Payment Instructions is Java Concurrent Program and Its not possible to view the XML from Diagnosis --> XML.

Below Query Return is the XML.

SELECT DOCUMENT 
FROM IBY_TRXN_DOCUMENTS 
WHERE PAYMENT_INSTRUCTION_ID = :P_PAYMENT_INSTRUCTION_ID;

Tuesday, April 29, 2014

Query to get Requisitions for which there is no PO

SELECT prh.segment1 ReqNum,
           prl.line_num lineNum,
           NVL(msi.segment1, prl.item_description) Item,
           prl.Quantity Qty,
           prl.suggested_vendor_name SuggSuppName,
           prl.need_by_date NeedDate,
           prh.authorization_status Status,
           pap.full_name Requestor
  FROM po_requisition_headers_all prh,
          per_all_people_f pap,
          po_requisition_lines_all prl,
          po_req_distributions_all prd,
          mtl_system_items msi
WHERE prh.preparer_id=pap.person_id
    AND prl.requisition_header_id = prh.requisition_header_id
    AND prd.requisition_line_id= prl.requisition_line_id
    AND msi.inventory_item_id (+) = prl.item_id
    AND msi.organization_id (+) = prl.destination_organization_id
    AND NOT EXISTS ( SELECT 1 
                                 FROM po_distributions_all pd
                               WHERE pd.req_distribution_id = prd.distribution_id)
  ORDER BY 1, 2

Query to get Items which are in Inspection.


SELECT poh.segment1 PoNUM,
              pol.line_num LineNum,
              msi.segment1 Item,
              rsh.receipt_num ReceiptNum,
              rct.quantity Quantity
      FROM apps.po_headers_all poh,
           apps.po_lines_all pol,
           apps.po_line_locations_all pll,
           apps.rcv_transactions rct,
           apps.rcv_shipment_headers rsh,
           apps.rcv_shipment_lines rsl,
           apps.rcv_routing_headers rrh,
           apps.mtl_system_items msi
     WHERE rsh.shipment_header_id = rsl.shipment_header_id
       AND rsl.po_line_location_id = rct.po_Line_location_id
       AND rsl.po_line_location_id = pll.Line_location_id
       AND rct.po_line_location_id = pll.Line_location_id
       AND poh.po_header_id = pol.po_header_id
       AND poh.po_header_id = rct.po_header_id
       AND pol.po_line_id = rct.po_line_id
       AND rct.transaction_type IN ('RECEIVE')
       AND rrh.routing_header_id=rct.routing_header_id
       AND rrh.routing_name='Inspection Required'
       AND msi.inventory_item_id=pol.item_id
       AND msi.organization_id=rct.organization_id
       AND NOT EXISTS
                     (SELECT 1
                        FROM apps.rcv_transactions rct1
                       WHERE 1=1
                         AND rct.transaction_id = rct1.parent_transaction_id
                         AND rct1.transaction_type = 'ACCEPT'
                      );