Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- select "PO Number2", "Total"
- from (select distinct "PO Number2", sum("Amount") as "Total"
- from (select distinct account as Account,
- company as Company,
- year_period_key as "Year Period Key",
- voucher_no as "Voucher No",
- voucher_type as "Vou Type",
- voucher_date as "GRN/Invoice Date",
- reference_number as "PO Number2",
- code_c as "Project ID",
- ifsapp.PURCHASE_ORDER_API.Get_Vendor_No(reference_number) as "Supplier ID",
- ifsapp.SUPPLIER_INFO_API.Get_Name(ifsapp.PURCHASE_ORDER_API.Get_Vendor_No(reference_number)) as "Supplier Name",
- sum(quantity) as "Quantity",
- sum(amount) as "Amount",
- sum(debet_amount) as "Debit Amount",
- sum(credit_amount) as "Credit Amount"
- from ifsapp.GEN_LED_VOUCHER_ROW_UNION_QRY
- where account = '222010'
- and Voucher_Type = '0'
- group by voucher_type,
- account,
- year_period_key,
- voucher_date,
- reference_number,
- code_c,
- company,
- voucher_no,
- party_type_id
- union all
- select distinct account as Account,
- company as Company,
- year_period_key as "Year Period Key",
- voucher_no as "Voucher No",
- voucher_type as "Vou Type",
- voucher_date as "GRN/Invoice Date",
- ifsapp.MAN_SUPP_INVOICE_API.Get_Purchase_Order_Ref_Number(company,
- party_type_id,
- 'Supplier',
- reference_number) as "PO Number2",
- code_c as "Project ID",
- ifsapp.PURCHASE_ORDER_API.Get_Vendor_No(ifsapp.MAN_SUPP_INVOICE_API.Get_Purchase_Order_Ref_Number(company,
- party_type_id,
- 'Supplier',
- reference_number)) as "Supplier ID",
- ifsapp.SUPPLIER_INFO_API.Get_Name(ifsapp.PURCHASE_ORDER_API.Get_Vendor_No(ifsapp.MAN_SUPP_INVOICE_API.Get_Purchase_Order_Ref_Number(company,
- party_type_id,
- 'Supplier',
- reference_number))) as "Supplier Name",
- sum(quantity) as "Quantity",
- sum(amount) as "Amount",
- sum(debet_amount) as "Debit Amount",
- sum(credit_amount) as "Credit Amount"
- from ifsapp.GEN_LED_VOUCHER_ROW_UNION_QRY
- where account = '222010'
- and Voucher_Type = 'J'
- group by voucher_type,
- account,
- year_period_key,
- voucher_date,
- reference_number,
- code_c,
- company,
- voucher_no,
- party_type_id)
- where "Year Period Key" <= '&YearPeriodKey'
- group by "PO Number2")
- where "Total" = 0
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement