project QadFinancials > class BMfgPLInvoice > business logic query PLInvoiceForMatchingPvo
Description
PLInvoiceForMatching: query that returns the pending vouchers based on purchase order shipper.
This query will return pending-voucher-detail (pvod_det) records with the underlaying tx2d_det records. So the reurn-set will contain multiple records for a single pvod_det in case there were multiple tax-rates applied to the pvod_det.
An index is added on field pvod_domain pvod_id pvod_id_line.
Parameters
| icDomainCode | character | |
| iiCompanyId | integer | Company id |
| icPOShipperInvoice | character | |
| icSupplier | character | |
| iiPliKeyId | integer | Key Id of purchase ledger invoice |
query condition
each pli_mstr where
pli_mstr.pli_domain = icDomainCode AND
pli_mstr.pli_invoice = icPOShipperInvoice AND
pli_mstr.pli_supplier = icSupplier AND
pli_mstr.pli_keyid = iiPliKeyId
each plid_det (inner-join) where
plid_det.plid_domain = pli_mstr.pli_domain AND
plid_det.plid_pli_keyid = pli_mstr.pli_keyid AND
first pvo_mstr (inner-join) where
pvo_mstr.pvo_domain = plid_det.plid_domain AND
pvo_mstr.pvo_id = plid_det.plid_pvo_id AND
pvo_mstr.pvo_last_voucher = ''
first prh_hist (outer-join) where
prh_hist.prh_domain = pvo_mstr.pvo_domain AND
prh_hist.prh_nbr = pvo_mstr.pvo_order AND
prh_hist.prh_receiver = pvo_mstr.pvo_internal_ref AND
prh_hist.prh_line = pvo_mstr.pvo_line AND
first pt_mstr (outer-join) where
pt_mstr.pt_domain = pvo_mstr.pvo_domain AND
pt_mstr.pt_part = pvo_mstr.pvo_part AND
each pvod_det (inner-join) where
pvod_det.pvod_domain = pvo_mstr.pvo_domain AND
pvod_det.pvod_id = pvo_mstr.pvo_id AND
first pod_det (inner-join) where
pod_det.pod_domain = pvod_det.pvod_domain AND
pod_det.pod_line = pvod_det.pvod_order_line AND
pod_det.pod_nbr = pvod_det.pvod_order AND
first po_mstr (inner-join) where
po_mstr.po_domain = pod_det.pod_domain AND
po_mstr.po_nbr = pod_det.pod_nbr AND
first si_mstr1 (outer-join) where
si_mstr1.si_domain = po_mstr.po_domain AND
si_mstr1.si_site = po_mstr.po_site AND
first en_mstr1 (outer-join) where
en_mstr1.en_domain = si_mstr1.si_domain AND
en_mstr1.en_entity = si_mstr1.si_entity AND
first Company1 (outer-join) where
Company1.Company_Id = iiCompanyId AND
Company1.CompanyCode = en_mstr1.en_entity AND
first si_mstr2 (inner-join) where
si_mstr2.si_domain = pod_det.pod_domain AND
si_mstr2.si_site = pod_det.pod_site AND
first en_mstr2 (inner-join) where
en_mstr2.en_domain = si_mstr2.si_domain AND
en_mstr2.en_entity = si_mstr2.si_entity AND
first Company2 (inner-join) where
Company2.CompanyCode = en_mstr2.en_entity AND
Company2.Company_ID = iiCompanyId
each tx2d_det (outer-join) where
tx2d_det.tx2d_domain = pvod_det.pvod_domain AND
((tx2d_det.tx2d_line = pvod_det.pvod_order_line AND
'01' = pvod_det.pvod_order_type) OR
(0 = pvod_det.pvod_order_line AND
pvod_det1.pvod_id_line = tx2d_det1.tx2d_line AND
'02' = pvod_det.pvod_order_type) OR
(tx2d_det.tx2d_line = 0 AND
'01' = pvod_det.pvod_order_type)) AND
tx2d_det.tx2d_ref = (if pvod_det.pvod_accrued_amt = 0 then pvod_det.pvod_internal_ref else string(pvod_det.pvod_id) ) AND
(tx2d_det.tx2d_tr_type = '45' OR
tx2d_det.tx2d_tr_type = '43' OR
tx2d_det.tx2d_tr_type = '21' OR
tx2d_det.tx2d_tr_type = '27' OR
tx2d_det.tx2d_tr_type = '25' OR
tx2d_det.tx2d_tr_type = '60' OR
tx2d_det.tx2d_tr_type = '30') AND
first tx2_mstr (outer-join) where
tx2_mstr.tx2_domain = tx2d_det.tx2d_domain AND
tx2_mstr.tx2_tax_code = tx2d_det.tx2d_tax_code AND
query resultset tqPLInvoiceForMatchingPvo
| field name | data type | db field | description |
|---|
| ttpvo_eff_date | date | pvo_mstr.pvo_eff_date | GL Effective Date |
| ttpvod_eff_date | date | pvod_det.pvod_eff_date | Effective Date |
| tipvod_order_line | integer | pvod_det.pvod_order_line | Order Line |
| tcpvod_order_type | character | pvod_det.pvod_order_type | Order Type |
| ttpvod_trans_date | date | pvod_det.pvod_trans_date | Transaction Date |
| tctx2_base | character | tx2_mstr.tx2_base | Taxable Base Code |
| tctx2d_ref | character | tx2d_det.tx2d_ref | Reference |
| tcpvo_supplier | character | pvo_mstr.pvo_supplier | Supplier |
| tlpvo_taxable | logical | pvo_mstr.pvo_taxable | Taxable |
| tltx2d_usage_tax_point | logical | tx2d_det.tx2d_usage_tax_point | Accrue Tax At Usage |
| tctx2d_tr_type | character | tx2d_det.tx2d_tr_type | Transaction Type |
| tdpvo_vouchered_amt | decimal | pvo_mstr.pvo_vouchered_amt | Vouchered Amt |
| tdtx2d_taxable_amt | decimal | tx2d_det.tx2d_taxable_amt | Base Taxable |
| tctx2d_tax_usage | character | tx2d_det.tx2d_tax_usage | Tax Usage |
| tctx2d_tax_type | character | tx2d_det.tx2d_tax_type | Tax Type |
| tltx2d_tax_in | logical | tx2d_det.tx2d_tax_in | Tax In |
| tctx2d_tax_env | character | tx2d_det.tx2d_tax_env | Tax Environment |
| tdtx2d_tax_amt | decimal | tx2d_det.tx2d_tax_amt | Base Tax |
| tltx2d_rcpt_tax_point | logical | tx2d_det.tx2d_rcpt_tax_point | Accrue Tax at Receipt |
| tdtx2d_abs_ret_amt | decimal | tx2d_det.tx2d_abs_ret_amt | Base Absorbed/Retained |
| tltx2_usage_tax_point | logical | tx2_mstr.tx2_usage_tax_point | Accrue Tax At Usage |
| tltx2_update_tax | logical | tx2_mstr.tx2_update_tax | Update Tax Allowed |
| tctx2_tax_code | character | tx2_mstr.tx2_tax_code | Tax Code |
| tltx2_stx_dltx_use | logical | tx2_mstr.tx2_stx_dltx_use | Suspended/Delayed Taxes |
| tltx2_reverse_charge | logical | tx2_mstr.tx2_reverse_charge | Domestic Reverse Charge |
| tltx2_rcpt_tax_point | logical | tx2_mstr.tx2_rcpt_tax_point | Accrue Tax at Receipt |
| tltx2_inv_disc | logical | tx2_mstr.tx2_inv_disc | Discount Tax at Invoice |
| tctx2_domain | character | tx2_mstr.tx2_domain | Domain |
| tctx2_dltx_inv_sub | character | tx2_mstr.tx2_dltx_inv_sub | Delayed Tax Invoice Sub-Account |
| tdpvo_accrued_amt | decimal | pvo_mstr.pvo_accrued_amt | Accrued Amount |
| tctx2_dltx_cn_sub | character | tx2_mstr.tx2_dltx_cn_sub | Delayed Tax CN Sub-Account |
| tctx2_dltx_cn_acct | character | tx2_mstr.tx2_dltx_cn_acct | Delayed Tax CN Account |
| tctx2_apr_sub | character | tx2_mstr.tx2_apr_sub | AP Retained Tax sub |
| tdpvod_pur_cost | decimal | pvod_det.pvod_pur_cost | PO Cost |
| tctx2_apr_cn_sub | character | tx2_mstr.tx2_apr_cn_sub | AP Tax Ret CN Sub-Account |
| tdpvod_trans_qty | decimal | pvod_det.pvod_trans_qty | Transaction Qty |
| tctx2_apr_cn_acct | character | tx2_mstr.tx2_apr_cn_acct | AP Tax Ret CN Account |
| tdpvod_vouchered_amt | decimal | pvod_det.pvod_vouchered_amt | Vouchered Amount |
| tctx2_apr_acct | character | tx2_mstr.tx2_apr_acct | AP Retained Tax Account |
| tdpvod_vouchered_qty | decimal | pvod_det.pvod_vouchered_qty | Vouchered Qty |
| tctx2_ap_sub | character | tx2_mstr.tx2_ap_sub | AP Tax Sub-Account |
| tcpvod_accrual_acct | character | pvod_det.pvod_accrual_acct | Accrual Account |
| tctx2_ap_cn_sub | character | tx2_mstr.tx2_ap_cn_sub | AP Tax CN Sub-Account |
| tcpvod_accrual_cc | character | pvod_det.pvod_accrual_cc | Accrual Cost Ctr |
| tctx2_ap_cn_acct | character | tx2_mstr.tx2_ap_cn_acct | AP Tax CN Account |
| tcpvod_accrual_sub | character | pvod_det.pvod_accrual_sub | Accrual Sub-Account |
| tctx2_ap_acct | character | tx2_mstr.tx2_ap_acct | AP Tax Account |
| tcpvo_order_type | character | pvo_mstr.pvo_order_type | Order Type |
| tdpvod_accrued_amt | decimal | pvod_det.pvod_accrued_amt | Accrued Amount |
| tcpo_cr_terms | character | po_mstr.po_cr_terms | Credit Terms |
| tdpvod_ex_rate | decimal | pvod_det.pvod_ex_rate | Exch Rate |
| tcPOMstrCompanyCode | character | Company1.CompanyCode | Entity Code |
| tdpvod_ex_rate2 | decimal | pvod_det.pvod_ex_rate2 | Exch Rate 2 |
| tiPOMstrCompanyID | integer | Company1.Company_ID | Record ID |
| tipvod_id_line | integer | pvod_det.pvod_id_line | Line |
| tiplid_pvo_id | integer | plid_det.plid_pvo_id | Pending Voucher ID |
| tcpvod_project | character | pvod_det.pvod_project | Project |
| tdprh_ps_qty | decimal | prh_hist.prh_ps_qty | Packing Slip Qty |
| tcpvod_domain | character | pvod_det.pvod_domain | Domain |
| tlpod_taxable | logical | pod_det.pod_taxable | Taxable |
| tipvod_id | integer | pvod_det.pvod_id | Pending Voucher ID |
| tctx2d_zone_to | character | tx2d_det.tx2d_zone_to | Ship-To Tax Zone |
| tctx2d_zone_from | character | tx2d_det.tx2d_zone_from | Ship-From Tax Zone |
| tctx2d_taxc | character | tx2d_det.tx2d_taxc | Tax Class |
| tctx2_dltx_inv_acct | character | tx2_mstr.tx2_dltx_inv_acct | Delayed Tax Invoice Account |
| tcpli_domain | character | pli_mstr.pli_domain | Domain |
| tcpli_invoice | character | pli_mstr.pli_invoice | Invoice Number |
| tipli_keyid | integer | pli_mstr.pli_keyid | Key ID |
| tcpli_supplier | character | pli_mstr.pli_supplier | Supplier |
| tiplid_line | integer | plid_det.plid_line | Line |
| tiplid_pli_keyid | integer | plid_det.plid_pli_keyid | Invoice Key ID |
| tcpvo_accrual_acct | character | pvo_mstr.pvo_accrual_acct | Accrual Account |
| tcpvo_accrual_cc | character | pvo_mstr.pvo_accrual_cc | Accrual Cost Ctr |
| tcpvo_accrual_sub | character | pvo_mstr.pvo_accrual_sub | Accrual Sub-Account |
| tcpvo_approver | character | pvo_mstr.pvo_approver | Approved By |
| tcpvo_buyer | character | pvo_mstr.pvo_buyer | Buyer |
| tcpvo_curr | character | pvo_mstr.pvo_curr | Currency |
| tcpvo_domain | character | pvo_mstr.pvo_domain | Domain |
| tcpvo_external_ref | character | pvo_mstr.pvo_external_ref | External Reference |
| tipvo_id | integer | pvo_mstr.pvo_id | Pending Voucher ID |
| tcpvo_internal_ref | character | pvo_mstr.pvo_internal_ref | Internal Reference |
| tcpvo_last_voucher | character | pvo_mstr.pvo_last_voucher | Voucher |
| tipvo_line | integer | pvo_mstr.pvo_line | Line |
| tcpvo_order | character | pvo_mstr.pvo_order | Order |
| tcpvo_part | character | pvo_mstr.pvo_part | Item Number |
| tcpvo_project | character | pvo_mstr.pvo_project | Project |
| tcpvo_shipto | character | pvo_mstr.pvo_shipto | Ship-To |
| ttpvo_trans_date | date | pvo_mstr.pvo_trans_date | Transaction Date |
| tdpvo_trans_qty | decimal | pvo_mstr.pvo_trans_qty | Transaction Qty |
| tcpt_desc1 | character | pt_mstr.pt_desc1 | Description |
| tcpt_desc2 | character | pt_mstr.pt_desc2 | Description |
| tdtx2_pct_recv | decimal | tx2_mstr.tx2_pct_recv | Percent Recoverable |
| tdtx2_tax_pct | decimal | tx2_mstr.tx2_tax_pct | Tax Rate |
| tdtx2d_cur_recov_amt | decimal | tx2d_det.tx2d_cur_recov_amt | Recoverable |
| tdtx2d_cur_tax_amt | decimal | tx2d_det.tx2d_cur_tax_amt | Tax |
| tdtx2d_totamt | decimal | tx2d_det.tx2d_totamt | Total Amount |
| tdtx2d_tottax | decimal | tx2d_det.tx2d_tottax | Taxable Sales |
| tcpod_vpart | character | pod_det.pod_vpart | Supplier Item |
| tiPODDetCompanyID | integer | Company2.Company_ID | Record ID |
| tcPODDetCompanyCode | character | Company2.CompanyCode | Entity Code |
| tcprh_type | character | prh_hist.prh_type | Purchase Type |
| tcprh_um | character | prh_hist.prh_um | UM |
| tdprh_um_conv | decimal | prh_hist.prh_um_conv | UM Conversion |
| tcprh_site | character | prh_hist.prh_site | Site |
| tltx2_tax_in | logical | tx2_mstr.tx2_tax_in | Allow Tax Included |
Internal usage
QadFinancials