Goods Received Note Report

GoodsReceived Note Report <?CF_ORG?>

<?xdoxslt:sysdate(‘HH24:mi:ss')?>

3/2/2014

Page 1 of 1

FL---<?for-each:G_REQ_NUMBER?>

Purchase Order           :		po--<?PURCHASE_ORDER?>		Account Code:		ac		Delivery Reference:
Goods Received Note :		grn--<?GOODS_RECEIVED_NOTE?>		Supplier Name:		sn		Delivery Date          :		cd
Item	Ordered	UOM	Part/Description		Customs Code	Outstanding	Received	Accepted	Rejected	Location	Requisition
SLRow_Num	qty	uom	Part Number - Descif Equipment ID : Equipment_ideif Project:Proj_num - PJ_NAME Task:Task Num -Task Name Destination Location -Dlocation		Customs Code	0.00	0.00	0.00	0.00	LOCATION	ReqSE
Received by:______________________________						Date:___________________

Page BreakFE

CF_ORG_NAME

                       C                     

*** No Data Found ***

EC

C  

*** End of Report ***

EC

Sql Query

----------------------

SELECT   poh.segment1 AS purchase_order,
           pov.segment1 account_code,
           pov.vendor_name AS supplier_name,
           rsh.receipt_num goods_received_note,
           rsh.shipment_header_id,
           rt.transaction_date,
           rt.organization_id
    FROM   rcv_transactions rt,
           rcv_shipment_headers rsh,
           rcv_shipment_lines rsl,
           po_headers_all poh,
           po_lines_all pol,
           po_line_locations poll,
           po_distributions_all pod,
           po_releases_all por,
           po_requisition_headers_all prh,
           po_requisition_lines prl,
           po_vendors pov
   --,apps.financials_system_params_all fsp
   WHERE       rsh.shipment_header_id = rt.shipment_header_id
           AND rsl.shipment_line_id = rt.shipment_line_id
           AND poh.po_header_id(+) = rt.po_header_id
           AND pol.po_line_id(+) = rt.po_line_id
           AND poll.line_location_id(+) = rt.po_line_location_id
           AND pod.po_distribution_id(+) = rt.po_distribution_id
           AND por.po_release_id(+) = rt.po_release_id
           AND prl.requisition_line_id(+) = rt.requisition_line_id
           AND prh.requisition_header_id(+) = prl.requisition_header_id
           AND poh.org_id = pol.org_id
           -- and nvl (rt.organization_id, fsp.inventory_organization_id) =
           --fsp.inventory_organization_id
           AND poh.org_id = pol.org_id
           AND poh.org_id = :p_operating_unit_id
--           AND poh.org_id = fnd_profile.VALUE ('org_id')
           AND pov.vendor_id = poh.vendor_id
           AND poh.segment1 BETWEEN NVL (:p_po_number_from, poh.segment1)
                                AND  NVL (:p_po_number_to, poh.segment1)
           AND poh.vendor_id BETWEEN NVL (:p_vendor_name_from, poh.vendor_id)
                                 AND  NVL (:p_vendor_name_to, poh.vendor_id)
           AND TRUNC (rt.transaction_date) BETWEEN NVL (
                                                      :p_transaction_from,
                                                      TRUNC (
                                                         rt.transaction_date
                                                      )
                                                   )
                                               AND  NVL (
                                                       :p_transaction_to,
                                                       TRUNC (
                                                          rt.transaction_date
                                                       )
                                                    )
           AND rsh.receipt_num BETWEEN NVL (:p_receipt_num_from,
                                            rsh.receipt_num)
                                   AND  NVL (:p_receipt_num_to,
                                             rsh.receipt_num)
           AND rt.organization_id =
                 NVL (:p_organization_id, rt.organization_id)
           AND rt.transaction_type = 'DELIVER'
GROUP BY poh.segment1
         ,pov.segment1
         ,pov.vendor_name
         ,rsh.receipt_num
         ,rsh.shipment_header_id,
           rt.transaction_date,
           rt.organization_id
ORDER BY   TO_NUMBER (rsh.receipt_num), poh.SEGMENT1

Formula Column

------------------------

function CF_orgFormula return Char is
   lc_org_name   VARCHAR2 (150);
BEGIN
   BEGIN
      SELECT NAME INTO lc_org_name
      FROM HR_ALL_ORGANIZATION_UNITS
      WHERE ORGANIZATION_ID=:P_OPERATING_UNIT_ID;
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         lc_org_name := NULL;
   END;
   RETURN (lc_org_name);
END;

Sql Query 2

-----------------------

SELECT   rsl.line_num,
         rsl.item_id,
         rsl.quantity_shipped,
         plla.quantity quantity_plla,
         plla.amount   amount_plla,
         plla.quantity_received quantity_received_plla,
         plla.amount_received   amount_received_plla,
         (plla.quantity-plla.quantity_received) outstanding_plla,
         (plla.amount-plla.amount_received) outstanding_amount_plla,
         rsl.quantity_received,
         rsl.amount_shipped,
         rsl.amount_received,
         (rsl.amount_shipped - rsl.amount_received) outstanding_amount,
         (rsl.quantity_shipped - rsl.quantity_received) outstanding,
         rsl.unit_of_measure,
         rsl.item_description,
         rsl.shipment_line_id,
         rsl.shipment_header_id,
         rsl.po_header_id,
         rsl.creation_date,
         rsl.po_line_id,
         rsl.to_organization_id,
         rt.attribute1 "custom code",
         msi.description "DESC",
         case when rsl.item_id is null then
             case when rt.attribute_category = 'UK Repairs' then
                        (select concatenated_segments from mtl_system_items_kfv
                         where  inventory_item_id   =   rt.attribute7
                         and    organization_id     =   rsl.to_organization_id)
                  when (rt.attribute_category like '%Rentals%' and rt.attribute_category<>'UK Rentals - Owned') then
                         rt.attribute7
             end
         else
            msi.concatenated_segments   
         end  "part number",
         ood.name "LOCATION",
         CASE
                 WHEN rt.attribute_category LIKE '%Rental%' THEN
                        rt.attribute8
                 WHEN rt.attribute_category LIKE '%Repair%' THEN
                        rt.attribute8
                 END EQUIPMENT_ID
  FROM   rcv_shipment_lines RSL,
         rcv_transactions RT,
         po_line_locations_all plla,
         mtl_system_items_kfv msi,--,apps.ORG_ORGANIZATION_DEFINITIONS OOD
         hr_all_organization_units ood
 WHERE       rt.SHIPMENT_LINE_ID = rsl.SHIPMENT_LINE_ID
         AND msi.INVENTORY_ITEM_ID(+) = rsl.ITEM_ID
         AND msi.organization_id(+) = rsl.to_organization_id
         AND rsl.po_line_location_id    = plla.line_location_id  
         AND rt.organization_id = ood.organization_id
         AND rt.TRANSACTION_TYPE = 'RECEIVE'
        AND  rsl.shipment_header_id=:shipment_header_id