DOWNLOAD

Goods Receipt Quantity Query in SCM Cloud

--Goods Receipt Quantity
SELECT Distinct
       pha.segment1                                                                              as "PO Number"
     , ps.Vendor_ID                                                                              as "Vendor Number"
     , ps.attribute1                                                                             as "Vendor Class"
     , ps.attribute2                                                                             as "Managing Office"
     , ps.ATTRIBUTE_NUMBER1                                                                      as "Supplier Legacy"
     , ps.vendor_name                                                                            as "Vendor Name"
     , rsl.VENDOR_ITEM_NUM                                                                       as "Vendor Item Num"
     , rsl.ITEM_DESCRIPTION                                                                      as "Item Description"
     , rsl.QUANTITY_SHIPPED                                                                      as "Quantity Shipped"
     , rsl.QUANTITY_RECEIVED                                                                     as "Quantity Received"
     , to_char( rct.TRANSACTION_DATE, 'DD/MM/YYYY')                                              as "Transaction Date"
     , pla.UNIT_PRICE* pla.QUANTITY                                                              as "Line Value"
     , HOU.Name                                                                                  as "Organization Name"
     , pla.UNIT_PRICE                                                                            as "Unit Price"
     , To_Char( pha.creation_date,'DD/MM/YYYY')                                                  as "PO Creation Date"
     , gl_flexfields_pkg.get_concat_description (chart_of_accounts_id, PDA.code_combination_id ) as "Code Combination Description"
     , gcc.segment1
              ||'.'
              ||gcc.segment2
              ||'.'
              ||gcc.segment3
              ||'.'
              || gcc.segment4
              ||'.'
              ||gcc.segment5
              ||'.'
              ||gcc.segment6
              ||'.'
              ||gcc.segment7
              ||'.'
              ||gcc.segment8
              ||'.'
              ||gcc.segment9
              ||'.'
              ||gcc.segment10 																	as "Concatenated Segments"
     , RSH.RECEIPT_NUM        																	as "Receipt Number"
     , pla.QUANTITY           																	as "PO Line Ordered Quantity"
     , CASE
              When pha.segment1 LIKE '4%'
                     THEN 'Franchise'
                     ELSE 'Company owned'
       End                                     													as "Business ID"
     , Null                                    													as "DS PO Indicator"
     , TO_CHAR( rct.Transaction_Date, 'MON' )  													as "Fiscal Period"
     , TO_CHAR( rct.Transaction_Date, 'YYYY' ) 													as "Fiscal Year"
     , TO_CHAR( sysdate, 'MON' )               													as "Regular Period"
     , TO_CHAR( sysdate, 'YYYY' )              													as "Regular Year"
     , Null                                    													as "PO_USER_FLD_1"
     , Null                                    													as "PO_USER_FLD_6"
     , rct.COMMENTS                            													as "Receipt Description"
FROM
       RCV_SHIPMENT_HEADERS 		RSH
     , RCV_SHIPMENT_LINES   		RSL
     , PO_HEADERS_ALL       		PHA
     , PER_PERSON_NAMES_F_V 		PersonName
     , PO_LINES_ALL         		pla
     , PO_DISTRIBUTIONS_ALL 		pda
     , PO_LINE_TYPES_B				PLTB
     , POZ_SUPPLIERS_V             	ps
     , POZ_SUPPLIER_SITES_V        	pss
     , RCV_TRANSACTIONS            	rct
     , HR_ALL_ORGANIZATION_UNITS_X 	HOU
     , GL_CODE_COMBINATIONS        	GCC
WHERE
       RSH.SHIPMENT_HEADER_ID			=RSL.SHIPMENT_HEADER_ID
       AND RSL.po_header_id				=PHA.po_header_id
       AND HOU.ORGANIZATION_ID			=rct.ORGANIZATION_ID
       AND GCC.CODE_COMBINATION_ID		=PDA.CODE_COMBINATION_ID
       AND PersonName.PERSON_ID			=PhA.agent_id
       AND pla.po_header_id				=PHA.po_header_id
       AND PDA.PO_DISTRIBUTION_ID		=rct.PO_DISTRIBUTION_ID
       AND PLTB.line_type_id			=pla.LINE_TYPE_ID
       AND pla.PO_LINE_ID				=rsl.PO_LINE_ID
       AND pha.VENDOR_ID				=ps.vendor_id
       AND pss.VENDOR_ID				=ps.vendor_id
       AND rct.SHIPMENT_HEADER_ID		=RSH.SHIPMENT_HEADER_ID
       AND rsl.SHIPMENT_LINE_ID			=rct.SHIPMENT_LINE_ID
       AND trunc( rct.TRANSACTION_DATE) between :Receipt_Date_From and :Receipt_Date_To
       AND ps.ATTRIBUTE_NUMBER1			=:Sup_Leg_No

Other Sample Queries

Quick Links

Useful Links

Contact Us

Phone Numbers

(USA) +1 678-248-5756
(EMEA) +44 (0) 7775 653956
(Toll-Free) +1 (844) 293-6669

Email Us

(USA) [email protected] (EMEA) [email protected]

© 2024 All Rights Reserved | Splash Business Intelligence Inc

Hello there
Request A Quote
Ready to Upgrade Your SQL Workflow?
Hello there
Looking for a partner to streamline your data tasks? SQL Connect is here to pull data from multiple Oracle ERP/SCM/HCM Pods. Run ad hoc queries, extract data to CSV or Excel, and make the most of your data. Discover the ease and efficiency of SQL Connect today!
Get Started
Hello there
Windows
Continue
MacOS
Select your version
Go Back
Reminder:
Don’t miss out on the year-end offer. Talk to us today!​
Hello there
YES
NO
Would you like to view the installation guide?
Go Back
Hello there
Step 1. Run the downloaded file to install SQL Connect on your computer.

Step 2: Register for Access
If you don’t have a username and password:
  • Click the “SIGN-UP” button on the login screen.
  • Complete the registration form.
  • Check your email for your login credentials.
Continue
installation guide
Go Back
Step 3. First Time Launch
Hello there
Open SQL Connect:
  • For Windows: Go to Start Menu -> Programs -> SQL Connect -> SQL Connect.
  • For Mac: Open Applications and find SQL Connect.
    Continue
    Go Back
    Logging In:
    • Enter the username and password provided by your administrator or received via email.
    • Note: Trial credentials expire after 15 days.
      Step 4. Creating a New Oracle Connection
      Hello there
      1. Open the Create Connection window in the application.
      2. Enter the following details:
        • Oracle Connection Name: Choose a name for this connection.
        • Oracle Cloud Username: Your user ID for Oracle Cloud.
        • Password: Your Oracle Cloud password. Use the EYE icon to show/hide the password.
        • URL: The web address for your Oracle pod, e.g., 'http://EEEE.us6.oraclecloud.oracle.com'
      3. Click "NEXT" to test and save the connection. If there's an error, check the details or contact your system administrator.
      Continue
      Go Back
      Step 5. Using SQL connect
      Hello there
      To connect to a database, select “Connect To” and pick your pod from the dropdown list.

      For any issues, refer to the FAQ or contact your administrator. If you are using an enterprise license, please get assistance from your company’s administrator.
      CLOSE GUIDE
      Go Back
      Hello there
      Step 1. Run the downloaded file to install SQL Connect on your computer.

      Step 2: Register for Access
      If you don’t have a username and password:
      • Click the “SIGN-UP” button on the login screen.
      • Complete the registration form.
      • Check your email for your login credentials.
      Continue
      installation guide
      Step 2. First Time Launch
      Hello there
      Open SQL Connect:
      • For Windows: Go to Start Menu -> Programs -> SQL Connect -> SQL Connect.
      • For Mac: Open Applications and find SQL Connect.
        Continue
        Go Back
        Logging In:
        • Enter the username and password provided by your administrator or received via email.
        • Note: Trial credentials expire after 15 days.
          Step 3. Creating a New Oracle Connection
          Hello there
          1. Open the Create Connection window in the application.
          2. Enter the following details:
            • Oracle Connection Name: Choose a name for this connection.
            • Oracle Cloud Username: Your user ID for Oracle Cloud.
            • Password: Your Oracle Cloud password. Use the EYE icon to show/hide the password.
            • URL: The web address for your Oracle pod, e.g., 'http://EEEE.us6.oraclecloud.oracle.com'
          3. Click "NEXT" to test and save the connection. If there's an error, check the details or contact your system administrator.
          Continue
          Go Back
          Step 4. Using SQL connect
          Hello there
          To connect to a database, select “Connect To” and pick your pod from the dropdown list.

          For any issues, refer to the FAQ or contact your administrator. If you are using an enterprise license, please get assistance from your company’s administrator.
          CLOSE GUIDE
          Go Back
          SQL - Year-End Offer