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


icDomainCodecharacter
iiCompanyIdintegerCompany id
icPOShipperInvoicecharacter
icSuppliercharacter
iiPliKeyIdintegerKey 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 namedata typedb fielddescription
ttpvo_eff_datedatepvo_mstr.pvo_eff_dateGL Effective Date
ttpvod_eff_datedatepvod_det.pvod_eff_dateEffective Date
tipvod_order_lineintegerpvod_det.pvod_order_lineOrder Line
tcpvod_order_typecharacterpvod_det.pvod_order_typeOrder Type
ttpvod_trans_datedatepvod_det.pvod_trans_dateTransaction Date
tctx2_basecharactertx2_mstr.tx2_baseTaxable Base Code
tctx2d_refcharactertx2d_det.tx2d_refReference
tcpvo_suppliercharacterpvo_mstr.pvo_supplierSupplier
tlpvo_taxablelogicalpvo_mstr.pvo_taxableTaxable
tltx2d_usage_tax_pointlogicaltx2d_det.tx2d_usage_tax_pointAccrue Tax At Usage
tctx2d_tr_typecharactertx2d_det.tx2d_tr_typeTransaction Type
tdpvo_vouchered_amtdecimalpvo_mstr.pvo_vouchered_amtVouchered Amt
tdtx2d_taxable_amtdecimaltx2d_det.tx2d_taxable_amtBase Taxable
tctx2d_tax_usagecharactertx2d_det.tx2d_tax_usageTax Usage
tctx2d_tax_typecharactertx2d_det.tx2d_tax_typeTax Type
tltx2d_tax_inlogicaltx2d_det.tx2d_tax_inTax In
tctx2d_tax_envcharactertx2d_det.tx2d_tax_envTax Environment
tdtx2d_tax_amtdecimaltx2d_det.tx2d_tax_amtBase Tax
tltx2d_rcpt_tax_pointlogicaltx2d_det.tx2d_rcpt_tax_pointAccrue Tax at Receipt
tdtx2d_abs_ret_amtdecimaltx2d_det.tx2d_abs_ret_amtBase Absorbed/Retained
tltx2_usage_tax_pointlogicaltx2_mstr.tx2_usage_tax_pointAccrue Tax At Usage
tltx2_update_taxlogicaltx2_mstr.tx2_update_taxUpdate Tax Allowed
tctx2_tax_codecharactertx2_mstr.tx2_tax_codeTax Code
tltx2_stx_dltx_uselogicaltx2_mstr.tx2_stx_dltx_useSuspended/Delayed Taxes
tltx2_reverse_chargelogicaltx2_mstr.tx2_reverse_chargeDomestic Reverse Charge
tltx2_rcpt_tax_pointlogicaltx2_mstr.tx2_rcpt_tax_pointAccrue Tax at Receipt
tltx2_inv_disclogicaltx2_mstr.tx2_inv_discDiscount Tax at Invoice
tctx2_domaincharactertx2_mstr.tx2_domainDomain
tctx2_dltx_inv_subcharactertx2_mstr.tx2_dltx_inv_subDelayed Tax Invoice Sub-Account
tdpvo_accrued_amtdecimalpvo_mstr.pvo_accrued_amtAccrued Amount
tctx2_dltx_cn_subcharactertx2_mstr.tx2_dltx_cn_subDelayed Tax CN Sub-Account
tctx2_dltx_cn_acctcharactertx2_mstr.tx2_dltx_cn_acctDelayed Tax CN Account
tctx2_apr_subcharactertx2_mstr.tx2_apr_subAP Retained Tax sub
tdpvod_pur_costdecimalpvod_det.pvod_pur_costPO Cost
tctx2_apr_cn_subcharactertx2_mstr.tx2_apr_cn_subAP Tax Ret CN Sub-Account
tdpvod_trans_qtydecimalpvod_det.pvod_trans_qtyTransaction Qty
tctx2_apr_cn_acctcharactertx2_mstr.tx2_apr_cn_acctAP Tax Ret CN Account
tdpvod_vouchered_amtdecimalpvod_det.pvod_vouchered_amtVouchered Amount
tctx2_apr_acctcharactertx2_mstr.tx2_apr_acctAP Retained Tax Account
tdpvod_vouchered_qtydecimalpvod_det.pvod_vouchered_qtyVouchered Qty
tctx2_ap_subcharactertx2_mstr.tx2_ap_subAP Tax Sub-Account
tcpvod_accrual_acctcharacterpvod_det.pvod_accrual_acctAccrual Account
tctx2_ap_cn_subcharactertx2_mstr.tx2_ap_cn_subAP Tax CN Sub-Account
tcpvod_accrual_cccharacterpvod_det.pvod_accrual_ccAccrual Cost Ctr
tctx2_ap_cn_acctcharactertx2_mstr.tx2_ap_cn_acctAP Tax CN Account
tcpvod_accrual_subcharacterpvod_det.pvod_accrual_subAccrual Sub-Account
tctx2_ap_acctcharactertx2_mstr.tx2_ap_acctAP Tax Account
tcpvo_order_typecharacterpvo_mstr.pvo_order_typeOrder Type
tdpvod_accrued_amtdecimalpvod_det.pvod_accrued_amtAccrued Amount
tcpo_cr_termscharacterpo_mstr.po_cr_termsCredit Terms
tdpvod_ex_ratedecimalpvod_det.pvod_ex_rateExch Rate
tcPOMstrCompanyCodecharacterCompany1.CompanyCodeEntity Code
tdpvod_ex_rate2decimalpvod_det.pvod_ex_rate2Exch Rate 2
tiPOMstrCompanyIDintegerCompany1.Company_IDRecord ID
tipvod_id_lineintegerpvod_det.pvod_id_lineLine
tiplid_pvo_idintegerplid_det.plid_pvo_idPending Voucher ID
tcpvod_projectcharacterpvod_det.pvod_projectProject
tdprh_ps_qtydecimalprh_hist.prh_ps_qtyPacking Slip Qty
tcpvod_domaincharacterpvod_det.pvod_domainDomain
tlpod_taxablelogicalpod_det.pod_taxableTaxable
tipvod_idintegerpvod_det.pvod_idPending Voucher ID
tctx2d_zone_tocharactertx2d_det.tx2d_zone_toShip-To Tax Zone
tctx2d_zone_fromcharactertx2d_det.tx2d_zone_fromShip-From Tax Zone
tctx2d_taxccharactertx2d_det.tx2d_taxcTax Class
tctx2_dltx_inv_acctcharactertx2_mstr.tx2_dltx_inv_acctDelayed Tax Invoice Account
tcpli_domaincharacterpli_mstr.pli_domainDomain
tcpli_invoicecharacterpli_mstr.pli_invoiceInvoice Number
tipli_keyidintegerpli_mstr.pli_keyidKey ID
tcpli_suppliercharacterpli_mstr.pli_supplierSupplier
tiplid_lineintegerplid_det.plid_lineLine
tiplid_pli_keyidintegerplid_det.plid_pli_keyidInvoice Key ID
tcpvo_accrual_acctcharacterpvo_mstr.pvo_accrual_acctAccrual Account
tcpvo_accrual_cccharacterpvo_mstr.pvo_accrual_ccAccrual Cost Ctr
tcpvo_accrual_subcharacterpvo_mstr.pvo_accrual_subAccrual Sub-Account
tcpvo_approvercharacterpvo_mstr.pvo_approverApproved By
tcpvo_buyercharacterpvo_mstr.pvo_buyerBuyer
tcpvo_currcharacterpvo_mstr.pvo_currCurrency
tcpvo_domaincharacterpvo_mstr.pvo_domainDomain
tcpvo_external_refcharacterpvo_mstr.pvo_external_refExternal Reference
tipvo_idintegerpvo_mstr.pvo_idPending Voucher ID
tcpvo_internal_refcharacterpvo_mstr.pvo_internal_refInternal Reference
tcpvo_last_vouchercharacterpvo_mstr.pvo_last_voucherVoucher
tipvo_lineintegerpvo_mstr.pvo_lineLine
tcpvo_ordercharacterpvo_mstr.pvo_orderOrder
tcpvo_partcharacterpvo_mstr.pvo_partItem Number
tcpvo_projectcharacterpvo_mstr.pvo_projectProject
tcpvo_shiptocharacterpvo_mstr.pvo_shiptoShip-To
ttpvo_trans_datedatepvo_mstr.pvo_trans_dateTransaction Date
tdpvo_trans_qtydecimalpvo_mstr.pvo_trans_qtyTransaction Qty
tcpt_desc1characterpt_mstr.pt_desc1Description
tcpt_desc2characterpt_mstr.pt_desc2Description
tdtx2_pct_recvdecimaltx2_mstr.tx2_pct_recvPercent Recoverable
tdtx2_tax_pctdecimaltx2_mstr.tx2_tax_pctTax Rate
tdtx2d_cur_recov_amtdecimaltx2d_det.tx2d_cur_recov_amtRecoverable
tdtx2d_cur_tax_amtdecimaltx2d_det.tx2d_cur_tax_amtTax
tdtx2d_totamtdecimaltx2d_det.tx2d_totamtTotal Amount
tdtx2d_tottaxdecimaltx2d_det.tx2d_tottaxTaxable Sales
tcpod_vpartcharacterpod_det.pod_vpartSupplier Item
tiPODDetCompanyIDintegerCompany2.Company_IDRecord ID
tcPODDetCompanyCodecharacterCompany2.CompanyCodeEntity Code
tcprh_typecharacterprh_hist.prh_typePurchase Type
tcprh_umcharacterprh_hist.prh_umUM
tdprh_um_convdecimalprh_hist.prh_um_convUM Conversion
tcprh_sitecharacterprh_hist.prh_siteSite
tltx2_tax_inlogicaltx2_mstr.tx2_tax_inAllow Tax Included


Internal usage


QadFinancials
method BAPMatching.GetPendingVouchersPOShipper
method BAPMatching.ValidateComponentPostAPM3PLI