Forum Stats

  • 3,784,023 Users
  • 2,254,873 Discussions
  • 7,880,652 Comments

Discussions

Combining two reports from two subject areas

User_O3GFE
User_O3GFE Member Posts: 5 Green Ribbon
edited Jan 14, 2022 3:59PM in HCM Cloud Service

Hi everyone,

I am trying to combine two reports with the SQL Code below. I was wondering if anyone could provide any help as to why it is not working.

SET VARIABLE PREFERRED_CURRENCY='User Preferred Currency 1';SELECT

  0 s_0,

  "Payables Invoices - Transactions Real Time"."- General Information"."Invoice Date" s_1,

  "Payables Invoices - Transactions Real Time"."- General Information"."Invoice Entered Date" s_2,

  "Payables Invoices - Transactions Real Time"."- General Information"."Invoice ID" s_3,

  "Payables Invoices - Transactions Real Time"."- Invoice Line Details - Income Tax and Withholding Information"."Type1099" s_4,

  "Payables Invoices - Transactions Real Time"."- Reference Information"."Payment Status Name" s_5,

  "Payables Invoices - Transactions Real Time"."- Supplier Site Location Details"."Site Address Address Line 1" s_6,

  "Payables Invoices - Transactions Real Time"."- Supplier Site Location Details"."Site Address Address Line 2" s_7,

  "Payables Invoices - Transactions Real Time"."- Supplier Site Location Details"."Site Address Address Line 3" s_8,

  "Payables Invoices - Transactions Real Time"."- Supplier Site Location Details"."Site Address Address Line 4" s_9,

  "Payables Invoices - Transactions Real Time"."- Supplier Site Location Details"."Site Address City" s_10,

  "Payables Invoices - Transactions Real Time"."- Supplier Site Location Details"."Site Address Country" s_11,

  "Payables Invoices - Transactions Real Time"."- Supplier Site Location Details"."Site Address Postal Code" s_12,

  "Payables Invoices - Transactions Real Time"."- Supplier Site Location Details"."Site Address State" s_13,

  "Payables Invoices - Transactions Real Time"."Business Unit"."Business Unit Name" s_14,

  "Payables Invoices - Transactions Real Time"."Business Unit"."Default Legal Entity Name" s_15,

  "Payables Invoices - Transactions Real Time"."Supplier"."Supplier Name" s_16,

  "Payables Invoices - Transactions Real Time"."Supplier"."Supplier Number" s_17,

  "Payables Invoices - Transactions Real Time"."Supplier"."Tax Organization Type" s_18,

  "Payables Invoices - Transactions Real Time"."Supplier"."Tax Payer ID" s_19,

  "Supplier - Supplier Real Time"."- Income Tax"."Federal Reportable" s_20,

  "Supplier - Supplier Real Time"."- Income Tax"."Supplier Income Tax type" s_21,

  DESCRIPTOR_IDOF("Payables Invoices - Transactions Real Time"."Business Unit"."Business Unit Name") s_22,

  DESCRIPTOR_IDOF("Payables Invoices - Transactions Real Time"."Supplier"."Tax Organization Type") s_23,

  left("Payables Invoices - Transactions Real Time"."- Distribution Combination"."Concatenated Segments",4) s_24,

  "Payables Invoices - Transactions Real Time"."- Invoice Amounts"."Invoice Amount" s_25

FROM "Payables Invoices - Transactions Real Time"

WHERE

((DESCRIPTOR_IDOF("Payables Invoices - Transactions Real Time"."Business Unit"."Business Unit Name") = 300000028208707) AND ("- Reference Information"."Payment Status Name" IN ('Fully paid', 'Partially paid')))

ORDER BY 1, 3 DESC NULLS FIRST, 2 ASC NULLS LAST, 25 ASC NULLS LAST, 16 ASC NULLS LAST, 15 ASC NULLS LAST, 17 ASC NULLS LAST, 18 ASC NULLS LAST, 20 ASC NULLS LAST, 19 ASC NULLS LAST, 7 ASC NULLS LAST, 8 ASC NULLS LAST, 9 ASC NULLS LAST, 10 ASC NULLS LAST, 11 ASC NULLS LAST, 14 ASC NULLS LAST, 13 ASC NULLS LAST, 12 ASC NULLS LAST, 6 ASC NULLS LAST, 21 ASC NULLS LAST, 22 ASC NULLS LAST, 5 ASC NULLS LAST, 4 ASC NULLS LAST

FETCH FIRST 250001 ROWS ONLY


INNER JOIN


SET VARIABLE PREFERRED_CURRENCY='User Preferred Currency 1';SELECT

  0 s_0,

  "Payables Payments - Disbursements Real Time"."- General Information"."Invoice ID" s_1,

  "Payables Payments - Disbursements Real Time"."- General Information"."Invoice Number" s_2,

  "Payables Payments - Disbursements Real Time"."- Other Information"."Pay Doc Payment Document Name" s_3,

  "Payables Payments - Disbursements Real Time"."- Payment Information"."Check Date" s_4,

  "Payables Payments - Disbursements Real Time"."- Payment Information"."Check Number" s_5,

  "Payables Payments - Disbursements Real Time"."- Payment Information"."Payment Status" s_6,

  "Payables Payments - Disbursements Real Time"."Business Unit"."Business Unit Name" s_7,

  DESCRIPTOR_IDOF("Payables Payments - Disbursements Real Time"."- Payment Information"."Payment Status") s_8,

  DESCRIPTOR_IDOF("Payables Payments - Disbursements Real Time"."Business Unit"."Business Unit Name") s_9,

  "Payables Payments - Disbursements Real Time"."- Invoice Installment Amounts"."Payment Amount" s_10

FROM "Payables Payments - Disbursements Real Time"

WHERE

((DESCRIPTOR_IDOF("Payables Payments - Disbursements Real Time"."- Payment Information"."Payment Status") <> 'VOIDED') AND (DESCRIPTOR_IDOF("Payables Payments - Disbursements Real Time"."Business Unit"."Business Unit Name") = 300000028208707))

ORDER BY 1, 5 DESC NULLS FIRST, 2 ASC NULLS LAST, 3 ASC NULLS LAST, 7 ASC NULLS LAST, 6 ASC NULLS LAST, 8 ASC NULLS LAST, 4 ASC NULLS LAST

FETCH FIRST 250001 ROWS ONLY


ON Payables Invoices - Transactions Real Time.invoice ID = Payables Payments - Disbursements Real Time.Invoice ID

Tagged: