project QadFinancials > class BAPMatching > business logic query APMatchingLnForPvodDetails
Description
APMatchingLnForPvodDetails: query used from the Calculate-method of the APMatching to verify if the pending-voucher data is still the same as one the moment the matching was saved.
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 APMatchingLN_ID
Parameters
| iiAPMatchingID | integer | APMatchingID |
| iiCompanyId | integer | Company id |
query condition
each APMatchingLn where
APMatchingLn.APMatching_ID = iiAPMatchingID
first pvod_det (inner-join) where
pvod_det.pvod_domain = APMatchingLn.PvoDomain AND
pvod_det.pvod_id = APMatchingLn.PvoID AND
pvod_det.pvod_id_line = APMatchingLn.PvodLineID 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 si_mstr (inner-join) where
si_mstr.si_domain = pod_det.pod_domain AND
si_mstr.si_site = pod_det.pod_site AND
first en_mstr (inner-join) where
en_mstr.en_domain = si_mstr.si_domain AND
en_mstr.en_entity = si_mstr.si_entity AND
first Company (inner-join) where
Company.Company_Id = iiCompanyId AND
Company.CompanyCode = en_mstr.en_entity AND
first pvo_mstr (inner-join) where
pvo_mstr.pvo_domain = pvod_det.pvod_domain AND
pvo_mstr.pvo_id = pvod_det.pvod_id AND
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 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 tqAPMatchingLnForPvodDetails
| field name | data type | db field | description |
|---|
| tiAPMatching_ID | integer | APMatchingLn.APMatching_ID | Link to APMatching |
| tiAPMatchingLn_ID | integer | APMatchingLn.APMatchingLn_ID | Record ID |
| tiCompany_ID | integer | Company.Company_ID | Record ID |
| tcCompanyCode | character | Company.CompanyCode | Entity Code |
| tcpod_loc | character | pod_det.pod_loc | Location |
| tlpod_taxable | logical | pod_det.pod_taxable | Taxable |
| tcpod_vpart | character | pod_det.pod_vpart | Supplier Item |
| tcprh_site | character | prh_hist.prh_site | Site |
| 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 |
| tcpt_desc1 | character | pt_mstr.pt_desc1 | Description |
| tcpt_desc2 | character | pt_mstr.pt_desc2 | Description |
| 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 |
| tdpvo_accrued_amt | decimal | pvo_mstr.pvo_accrued_amt | Accrued Amount |
| 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 |
| tcpvo_lc_charge | character | pvo_mstr.pvo_lc_charge | Logistics Charge Code |
| tipvo_line | integer | pvo_mstr.pvo_line | Line |
| tcpvo_order | character | pvo_mstr.pvo_order | Order |
| tcpvo_order_type | character | pvo_mstr.pvo_order_type | Order Type |
| tcpvo_part | character | pvo_mstr.pvo_part | Item Number |
| tcpvo_project | character | pvo_mstr.pvo_project | Project |
| tcpvo_shipfrom | character | pvo_mstr.pvo_shipfrom | Ship-From |
| tcpvo_shipto | character | pvo_mstr.pvo_shipto | Ship-To |
| tcpvo_supplier | character | pvo_mstr.pvo_supplier | Supplier |
| ttpvo_trans_date | date | pvo_mstr.pvo_trans_date | Transaction Date |
| tdpvo_trans_qty | decimal | pvo_mstr.pvo_trans_qty | Transaction Qty |
| tdpvo_vouchered_amt | decimal | pvo_mstr.pvo_vouchered_amt | Vouchered Amt |
| tdpvo_vouchered_qty | decimal | pvo_mstr.pvo_vouchered_qty | Vouchered Qty |
| tcpvod_accrual_acct | character | pvod_det.pvod_accrual_acct | Accrual Account |
| tcpvod_accrual_cc | character | pvod_det.pvod_accrual_cc | Accrual Cost Ctr |
| tcpvod_accrual_sub | character | pvod_det.pvod_accrual_sub | Accrual Sub-Account |
| tdpvod_accrued_amt | decimal | pvod_det.pvod_accrued_amt | Accrued Amount |
| tcpvod_domain | character | pvod_det.pvod_domain | Domain |
| ttpvod_eff_date | date | pvod_det.pvod_eff_date | Effective Date |
| tdpvod_ex_rate | decimal | pvod_det.pvod_ex_rate | Exch Rate |
| tdpvod_ex_rate2 | decimal | pvod_det.pvod_ex_rate2 | Exch Rate 2 |
| tipvod_id | integer | pvod_det.pvod_id | Pending Voucher ID |
| tipvod_id_line | integer | pvod_det.pvod_id_line | Line |
| tcpvod_last_voucher | character | pvod_det.pvod_last_voucher | Supplier Invoice |
| tipvod_order_line | integer | pvod_det.pvod_order_line | Order Line |
| tcpvod_order_type | character | pvod_det.pvod_order_type | Order Type |
| tdpvod_pur_cost | decimal | pvod_det.pvod_pur_cost | PO Cost |
| ttpvod_trans_date | date | pvod_det.pvod_trans_date | Transaction Date |
| tdpvod_trans_qty | decimal | pvod_det.pvod_trans_qty | Transaction Qty |
| tdpvod_vouchered_qty | decimal | pvod_det.pvod_vouchered_qty | Vouchered Qty |
| tcsi_domain | character | si_mstr.si_domain | Domain |
| tcsi_entity | character | si_mstr.si_entity | Entity |
| tctx2_ap_acct | character | tx2_mstr.tx2_ap_acct | AP Tax Account |
| tctx2_ap_cn_acct | character | tx2_mstr.tx2_ap_cn_acct | AP Tax CN Account |
| tctx2_ap_cn_sub | character | tx2_mstr.tx2_ap_cn_sub | AP Tax CN Sub-Account |
| tctx2_ap_sub | character | tx2_mstr.tx2_ap_sub | AP Tax Sub-Account |
| tctx2_apr_acct | character | tx2_mstr.tx2_apr_acct | AP Retained Tax Account |
| tctx2_apr_cn_acct | character | tx2_mstr.tx2_apr_cn_acct | AP Tax Ret CN Account |
| tctx2_apr_cn_sub | character | tx2_mstr.tx2_apr_cn_sub | AP Tax Ret CN Sub-Account |
| tctx2_apr_sub | character | tx2_mstr.tx2_apr_sub | AP Retained Tax sub |
| tctx2_dltx_cn_acct | character | tx2_mstr.tx2_dltx_cn_acct | Delayed Tax CN Account |
| tctx2_dltx_cn_sub | character | tx2_mstr.tx2_dltx_cn_sub | Delayed Tax CN Sub-Account |
| tctx2_dltx_inv_acct | character | tx2_mstr.tx2_dltx_inv_acct | Delayed Tax Invoice Account |
| tctx2_dltx_inv_sub | character | tx2_mstr.tx2_dltx_inv_sub | Delayed Tax Invoice Sub-Account |
| tctx2_domain | character | tx2_mstr.tx2_domain | Domain |
| tltx2_inv_disc | logical | tx2_mstr.tx2_inv_disc | Discount Tax at Invoice |
| tdtx2_pct_recv | decimal | tx2_mstr.tx2_pct_recv | Percent Recoverable |
| tltx2_rcpt_tax_point | logical | tx2_mstr.tx2_rcpt_tax_point | Accrue Tax at Receipt |
| tltx2_reverse_charge | logical | tx2_mstr.tx2_reverse_charge | Domestic Reverse Charge |
| tltx2_stx_dltx_use | logical | tx2_mstr.tx2_stx_dltx_use | Suspended/Delayed Taxes |
| tctx2_tax_code | character | tx2_mstr.tx2_tax_code | Tax Code |
| tdtx2_tax_pct | decimal | tx2_mstr.tx2_tax_pct | Tax Rate |
| tltx2_update_tax | logical | tx2_mstr.tx2_update_tax | Update Tax Allowed |
| tltx2_usage_tax_point | logical | tx2_mstr.tx2_usage_tax_point | Accrue Tax At Usage |
| tdtx2d_abs_ret_amt | decimal | tx2d_det.tx2d_abs_ret_amt | Base Absorbed/Retained |
| tdtx2d_cur_recov_amt | decimal | tx2d_det.tx2d_cur_recov_amt | Recoverable |
| tdtx2d_cur_tax_amt | decimal | tx2d_det.tx2d_cur_tax_amt | Tax |
| tltx2d_rcpt_tax_point | logical | tx2d_det.tx2d_rcpt_tax_point | Accrue Tax at Receipt |
| tctx2d_ref | character | tx2d_det.tx2d_ref | Reference |
| tdtx2d_tax_amt | decimal | tx2d_det.tx2d_tax_amt | Base Tax |
| tctx2d_tax_code | character | tx2d_det.tx2d_tax_code | Tax Code |
| tctx2d_tax_env | character | tx2d_det.tx2d_tax_env | Tax Environment |
| tctx2d_tax_type | character | tx2d_det.tx2d_tax_type | Tax Type |
| tctx2d_tax_usage | character | tx2d_det.tx2d_tax_usage | Tax Usage |
| tdtx2d_taxable_amt | decimal | tx2d_det.tx2d_taxable_amt | Base Taxable |
| tctx2d_taxc | character | tx2d_det.tx2d_taxc | Tax Class |
| tdtx2d_totamt | decimal | tx2d_det.tx2d_totamt | Total Amount |
| tdtx2d_tottax | decimal | tx2d_det.tx2d_tottax | Taxable Sales |
| tctx2d_tr_type | character | tx2d_det.tx2d_tr_type | Transaction Type |
| tltx2d_usage_tax_point | logical | tx2d_det.tx2d_usage_tax_point | Accrue Tax At Usage |
| tctx2d_zone_from | character | tx2d_det.tx2d_zone_from | Ship-From Tax Zone |
| tctx2d_zone_to | character | tx2d_det.tx2d_zone_to | Ship-To Tax Zone |
Internal usage
QadFinancials