Oralce ERS Invoice Processing

AIM

MD.050 Application Extensions Functional Design

Hudson’s Bay Company

Oracle ERS invoicing process

Author:                         Oleg Margolin   

Creation Date:            October 16, 2012

Last Updated:             July 2, 2013

Document Ref:          

Version:                        V1.0

Approvals:

Copy Number    _____

Document Control

Change Record

3

Date	Author	Version	Change Reference
	Ol
October 16 2012	Oleg Margolin	Draft 1.0	No Previous Document
Apr 2013	Oleg Margolin		Move location of quantity and price fields to global_attributes

Reviewers

Name	Position
Janice Lusanne	Financial Systems Manager
Ghazi Omran

Distribution

Copy No.	Name	Location
1	Library Master	Project Library
2		Project Manager
3
4

Note To Holders:

If you receive an electronic copy of this document and print it out, please write your name on the equivalent of the cover page, for document control purposes.

If you receive a hard copy of this document, please write your name on the front cover, for document control purposes.

Contents

Document Control................................................................................................................................ ii

Topical Essay.......................................................................................................................................... 1

Basic Business Needs................................................................................................................... 1

Major Features................................................................................................................................ 4

User Procedures............................................................................................................................. 4

Business Rules............................................................................................................................... 4

Assumptions.................................................................................................................................. 4

Technical Overview.............................................................................................................................. 5

Form and Report Descriptions.......................................................................................................... 6

Form to move pending 3WAY receipts to AP (XXHBC_3WAY_ERS).......................... 6

Concurrent Program Description (HBC Process Receipts Package Name: XXHBC_3WAY_ALL_RECEIPT)..................................................................................................... 1

When to Run the Program.......................................................................................................... 1

Log Output...................................................................................................................................... 1

Restart Procedures........................................................................................................................ 1

Technical Overview...................................................................................................................... 1

Logic Summary.............................................................................................................................. 3

Payment remittance process ELECTRONIC (EDI820) – xxhbc_3way_edi820_pkg......... 7

Consignment invoice EDI820 mapping............................................................................... 17

When to Run the Program........................................................................................................ 20

Launch Parameters.................................................................................................................... 20

Restart Procedures...................................................................................................................... 21

Payment remittance process PAPER (EDI820) – xxhbc_edi820_remittance.................... 22

When to Run the Program.......................................................................................................... 1

Launch Parameters....................................................................................................................... 1

Restart Procedures........................................................................................................................ 1

Open and Closed Issues for this Deliverable................................................................................ 2

Open Issues..................................................................................................................................... 2

Closed Issues.................................................................................................................................. 2

Topical Essay

There is a need to move all merchandise invoicing from Retek (ERS) to Oracle ERP system.  This will allow elimination of the ERS process from Retek.  This change will also allow easier movement of invoices from 3way match process to ERS.

Basic Business Needs

All merchandise receipts will be interfaced to Oracle and the system will determine whether to pay the receipts through AP directly or, if the vendor is 3way match type of vendor, wait for the EDI810 invoice from the vendor.  For these 3way vendors, the receipts will flow directly to the existing 3way match system.

Current Process

New Process

Major Features

i.         Staging table of all receipts will be populated by Retek daily.  These will be interfaced directly to AP or wait for an EDI810 invoice from the vendor.  The split is based on vendors’ EDI810 effective date.

ii.       Allow the user to move a receipt from 3way match process and pay it directly by AP.

iii.     All EDI820 remittances will be generated from Oracle.  The transactions will be at SKU level as required by vendors.

iv.     Tax amounts on non-3way match invoices (ERS) will now be calculated in Oracle based on tax code and Province provided on Receipt interface.  Oracle Tax rates will be used.

v.       Will remove “Tester” skus from receipts passed from Retek into 3way match and ERS process.

User Procedures

The Oracle Concurrent manager process will run through ESP.  This process will run daily to process the Receipt file.  Receipts will be moved directly to AP or to 3way match process.  This will be determined by the vendor’s EDI810 effective date.

User will have a screen to move receipts from 3way match to AP directly (as ERS) if required.

Business Rules

Assumptions

Technical Overview

A new table (XXHBC_RECEIPTS) will be created to hold ALL merchandise receipts.  This table will be processed daily (HBC Process Receipts) to determine if these receipts are for 3-way match process or need to be directly paid (old ERS).  For non-3way receipts, the invoices and off-po adjustments will be inserted to Oracle AP Open interface table.

EDI820 process will now include all AP invoices and details (when available) on the interface.  The current 3way EDI820 process (HBC 3Way EDI820 Payment Outbound) will be modified to include all vendor payments and details.

Form and Report Descriptions

Form to move pending 3WAY receipts to AP (XXHBC_3WAY_ERS)

New form to move receipts from 3way pending receipts directly to AP for payment will be developed.  Create a screen that allows user to query XXHBC_3WAY_PO_SHIP_IFACE table records.  Only show shipments which have po_line_locations_all Quantity_invoiced = 0

·         Allow user to query the table to find the required records to move to AP.  Allow Query by Site number, ASN, PO number, Location.  Any or all these fields can be queried on.  Meaning user can enter any of the fields to search on and click ‘SEARCH’ button

·         After SEARCH button is pressed, show ALL details for this selection to confirm the correct detail data is found.  Note: Calculate Total column as Unit_price * Quantity Received.  The Total Selected field is the total of all Total lines.

·         User can then press the ‘PAY BY ERS’ button to move the shipment to pay through AP without waiting for EDI810 invoice. 

·         Ask user to confirm selection.  Message:”(Are you sure you want to Move these records to ERS payment?”

·         If user confirms, insert the selected details selected to XXHBC_RECEIPTS with STATUS = “ERS’.  Keep the creation_date field same as on XXHBC_3WAY_PO_SHIP_IFACE table.  PO_APPROVE_DATE can be sysdate.

·         Delete these records from XXHBC_3WAY_PO_SHIP_IFACE, po_line_locations_all, po_line_distributions.

Concurrent Program Description (HBC Process Receipts Package Name: XXHBC_3WAY_ALL_RECEIPT)

The HBC Process Receipts concurrent program is needed process all receipts from Retek on a daily basis.  The split will be based on the vendor’s EDI810 effective date and the PO approve date sent on the receipt interface table.  

When to Run the Program

Program will run daily

Log Output

The log output consists of standard record counts and program execution statements.

Restart Procedures

Restart procedures for concurrent program are as follows:

·         Rerun program.

Technical Overview

Retek system will populate the Oracle ERP custom table XXHBC_RECEIPTS daily.  This table will be processed by the HBC Process Receipts request to ‘split’ the receipts into either 3way or non-3way receipts based on the PO Approve date (on receipt record) and the 3way Match Effective Date (on vendor site record).  3way receipts will be placed into XXHBC_3WAY_PO_SHIP_IFACE table for regular (existing) 3way processing.  Non-3way receipts will be placed directly onto AP Open interface tables.  Off-PO discounts will also be calculated and placed on the Open Interface tables. Taxes are to be calculated if required.

XXHBC_RECEIPTS table definition

CREATE TABLE XXHBC_RECEIPTS

(

  PO_NUMBER                                                      VARCHAR2(20 BYTE) NOT NULL,

  BUYER_USER_ID                                                VARCHAR2(100 BYTE) NOT NULL,

  VENDOR_SITE_CODE                                       VARCHAR2(15 BYTE) NOT NULL,

  TERM_DUE_PERCENT                                      NUMBER,

  TERM_DISCOUNT_MONTHS_FORWARD     NUMBER,

  TERM_DUE_DAYS                                             NUMBER,

  TERM_DISCOUNT_PERCENT                                          NUMBER,

  TERM_DISCOUNT_DAYS                                 NUMBER,

  TERM_DISCOUNT_DAY_OF_THE_MONTH                  NUMBER,

  TERM_IDENTIFIER                                            VARCHAR2(2 BYTE),

  CURRENCY_CODE                                            VARCHAR2(15 BYTE) NOT NULL,

  ITEM_DESCRIPTION                                         VARCHAR2(240 BYTE) NOT NULL,

  UNIT_PRICE                                       NUMBER        NOT NULL,

  SKU_NUMBER                                                    NUMBER        NOT NULL,

  TAXABLE_FLAG                                                VARCHAR2(3 BYTE) NOT NULL,

  QUANTITY_ORDERED                                     NUMBER        NOT NULL,

  QUANTITY_RECEIVED                                     NUMBER        NOT NULL,

  QUANTITY_SHIPPED                                       NUMBER        NOT NULL,

  SHIPMENT_NUM                                               VARCHAR2(100 BYTE) NOT NULL,

  UOM                                                                     VARCHAR2(25 BYTE) NOT NULL,

  SHIP_TO_LOCATION                                       NUMBER        NOT NULL,

  SET_OF_BOOKS_ID                                           NUMBER        NOT NULL,

  GL_ACCRUAL_SEGMENT1                              VARCHAR2(25 BYTE) NOT NULL,

  GL_ACCRUAL_SEGMENT2                              VARCHAR2(25 BYTE) NOT NULL,

  GL_ACCRUAL_SEGMENT3                              VARCHAR2(25 BYTE) NOT NULL,

  GL_ACCRUAL_SEGMENT4                              VARCHAR2(25 BYTE) NOT NULL,

  GL_ACCRUAL_SEGMENT5                              VARCHAR2(25 BYTE) NOT NULL,

  GL_ACCRUAL_SEGMENT6                              VARCHAR2(25 BYTE),

  CREATION_DATE                                             DATE          NOT NULL,

  EXTRACT_FLAG                                                 VARCHAR2(3 BYTE),

  REJECT_REASON                                               VARCHAR2(240 BYTE),

  EXTRACT_DATE                                                VARCHAR2(10 BYTE),

  GOODS_RECEIVED_DATE                               VARCHAR2(20 BYTE) NOT NULL,

  BOL_NUMBER                                                    VARCHAR2(30 BYTE),

  TAX_PROVINCE                                                 VARCHAR2(2 BYTE) NOT NULL,

  TAX_GST                                                             VARCHAR2(1 BYTE),

  TAX_QST                                                             VARCHAR2(1 BYTE),

  TAX_HST                                                             VARCHAR2(1 BYTE),

  RETEK_SHIPMENT                                            NUMBER,

  DISCOUNT1_PCT                                              NUMBER,

  DISCOUNT1_NAME                                          VARCHAR2(19 BYTE),

  DISCOUNT1_CODE                                           VARCHAR2(4 BYTE),

  DISCOUNT2_PCT                                              NUMBER,

  DISCOUNT2_NAME                                          VARCHAR2(19 BYTE),

  DISCOUNT2_CODE                                           VARCHAR2(4 BYTE),

  DISCOUNT3_PCT                                              NUMBER,

  DISCOUNT3_NAME                                          VARCHAR2(19 BYTE),

  DISCOUNT3_CODE                                           VARCHAR2(4 BYTE),

  DISCOUNT4_PCT                                              NUMBER,

  DISCOUNT4_NAME                                          VARCHAR2(19 BYTE),

  DISCOUNT4_CODE                                           VARCHAR2(4 BYTE),

  DISCOUNT5_PCT                                              NUMBER,

  DISCOUNT5_NAME                                          VARCHAR2(19 BYTE),

  DISCOUNT5_CODE                                           VARCHAR2(4 BYTE),

  DISCOUNT6_PCT                                              NUMBER,

  DISCOUNT6_NAME                                          VARCHAR2(19 BYTE),

  DISCOUNT6_CODE                                           VARCHAR2(4 BYTE),

  DISCOUNT7_PCT                                              NUMBER,

  DISCOUNT7_NAME                                          VARCHAR2(19 BYTE),

  DISCOUNT7_CODE                                           VARCHAR2(4 BYTE),

  DISCOUNT8_PCT                                              NUMBER,

  DISCOUNT8_NAME                                          VARCHAR2(19 BYTE),

  DISCOUNT8_CODE                                           VARCHAR2(4 BYTE),

  DISCOUNT9_PCT                                              NUMBER,

  DISCOUNT9_NAME                                          VARCHAR2(19 BYTE),

  DISCOUNT9_CODE                                           VARCHAR2(4 BYTE),

  DISCOUNT10_PCT                                            NUMBER,

  DISCOUNT10_NAME                                        VARCHAR2(19 BYTE),

  DISCOUNT10_CODE                                         VARCHAR2(4 BYTE),

  DISCOUNT11_PCT                                            NUMBER,

  DISCOUNT11_NAME                                        VARCHAR2(19 BYTE),

  DISCOUNT11_CODE                                         VARCHAR2(4 BYTE),

  DISCOUNT12_PCT                                            NUMBER,

  DISCOUNT12_NAME                                        VARCHAR2(19 BYTE),

  DISCOUNT12_CODE                                         VARCHAR2(4 BYTE),

  DISCOUNT13_PCT                                            NUMBER,

  DISCOUNT13_NAME                                        VARCHAR2(19 BYTE),

  DISCOUNT13_CODE                                         VARCHAR2(4 BYTE),

  DISCOUNT14_PCT                                            NUMBER,

  DISCOUNT14_NAME                                        VARCHAR2(19 BYTE),

  DISCOUNT14_CODE                                         VARCHAR2(4 BYTE),

  DISCOUNT15_PCT                                            NUMBER,

  DISCOUNT15_NAME                                        VARCHAR2(19 BYTE),

  DISCOUNT15_CODE                                         VARCHAR2(4 BYTE),

  MASTER_BOL_NUMBER                                   VARCHAR2(30 BYTE),

  PO_APPROVE_DATE                                        DATE          NOT NULL,

  STATUS                                                               VARCHAR2(30 bytes),

LAST_UPDATE_DATE DATE

)

ALTER TABLE  XXHBC_RECEIPTS ADD (

  CONSTRAINT CHECK_CURRENCY_CODE

 CHECK (CURRENCY_CODE='USD' or CURRENCY_CODE='CAD'));

ALTER TABLE  XXHBC_RECEIPTS ADD (

  CONSTRAINT CHECK_EXTRACT_FLAG

 CHECK (EXTRACT_FLAG ='Y' or EXTRACT_FLAG ='R' or EXTRACT_FLAG is null ));

ALTER TABLE XXHBC_RECEIPTS ADD (

  CONSTRAINT CHECK_GL_ACCRUAL_SEGMENT6

 CHECK (GL_ACCRUAL_SEGMENT6='000000' or GL_ACCRUAL_SEGMENT6 is null));

ALTER TABLE XXHBC_RECEIPTS ADD (

  CONSTRAINT CHECK_TAXABLE_FLAG

 CHECK (taxable_flag='Y' or taxable_flag='N'));

ALTER TABLE XXHBC_RECEIPTS ADD (

  CONSTRAINT CHECK_TAX_GST

 CHECK (TAX_GST='Y' or TAX_GST='N' or TAX_GST is null ));

ALTER TABLE XXHBC_RECEIPTS ADD (

  CONSTRAINT CHECK_TAX_HST

 CHECK (TAX_HST='Y' or TAX_HST='N' or TAX_HST is null ));

ALTER TABLE XXHBC_RECEIPTS ADD (

  CONSTRAINT CHECK_TAX_PROVINCE

 CHECK (TAX_PROVINCE ='AB'

        or TAX_PROVINCE ='BC'

        or TAX_PROVINCE='MB'

        or TAX_PROVINCE='NB'

        or TAX_PROVINCE='NF'

        or TAX_PROVINCE='NS'

        or TAX_PROVINCE='ON'

        or TAX_PROVINCE='PE'

        or TAX_PROVINCE='QE'

        or TAX_PROVINCE='SK'));

ALTER TABLE XXHBC_RECEIPTS ADD (

  CONSTRAINT CHECK_TAX_QST

 CHECK (TAX_QST='Y' or TAX_QST='N' or TAX_QST is null ));

ALTER TABLE XXHBC_RECEIPTS ADD (

  CONSTRAINT CHECK_UNIT_PRICE

 CHECK (unit_price>0));

ALTER TABLE XXHBC_RECEIPTS ADD (

  CONSTRAINT CHECK_UOM

 CHECK (UOM='EACH'));

XXHBC_RECEIPTS_TESTERS table definition

Table XXHBC_RECEIPTS_TESTERS will have same fields as XXHBC_RECEIPTS table.  It will hold only tester SKUS that were removed from the receipt.

Logic Summary

Loop for all XXHBC_RECEIPTS records where UNIT_PRICE < .01

    Copy this record to XXHBC_RECEIPTS_TESTERS

    Delete this record from XXHBC_RECEIPTS

End loop

COMMIT

Loop for all XXHBC_RECEIPTS records where STATUS = null

    If PO_APPROVE_DATE >= edi810_effective_date (from xxhbc_sites_all for SITE)

          Set STATUS = ‘3WAY’

    Else set STATUS = ‘ERS’

End loop

COMMIT

Loop XXHBC_RECEIPTS records where STATUS = ‘3WAY’ or ‘ERS’

    If STATUS = ‘3WAY’

          Insert record into XXHBC_3WAY_PO_SHIP_IFACE

Set STATUS = ‘PROCESSED 3WAY’

    Else STATUS = ‘ERS’

          Calculate TAX based on tax flags and province        

Calculate off-po discount

Calculate TAX for off-PO discount

Insert Invoice and off-po adjustments into AP OIT tables

Set STATUS = ‘PROCESSED ERS’

    End if

    Set Last_update_date = Sysdate

End loop

Loading ERS invoices to AP Open Interface tables

Oracle AP Open Interface tables must be populated creating ERS invoices.   This is done to create invoices based on interface table xxhbc_Receipts STATUS = ‘ERS’.

OFF_PO adjustments (Debits)

If there are off-po discounts on the xxhbc_receipts table, these must be created on the AP Open Interface as separate invoices (Debits).  Set Transaction code (Attribute4) on AP_INVOICES_INTERFACE to ‘900’ for all off-po adjustments.  If taxes apply to the SKU, calculate it and insert it as part of the off-po debit

Invoice header DESCRIPTION for these adjustments need to have the DISCOUNT_NAME and DISCOUNT_PERCENTAGE.

Invoice_num for the adjustments are to have the ERS Invoice_number||AD# where # is the discount sequence.

Tax calculations

Calculate the tax amount on the invoice based on the Province code and tax type coming in on the xxhbc_receipts table.  Date used for Rate is the Goods_received_date on the receipt.  Look up the rate on the AP_Tax tables to get current tax code and calculate the amount.  Taxes also need to be calculated for the off-PO adjustments.

Taxes are to be derived by SKU then summarized on invoice by tax type. Use TAX_PROVINCE, TAX_GST, TAX_QST, TAX_HST on each detail SKU record to get the tax code.  Use the code to get the rate for the applicable tax.  Calculate tax amount based on this rate.  Summarize the item (sku) taxes by tax type and create a TAX line on Invoice distribution for each type.

When determining TAX code/rate, if you are not able to find correct combination in TAX tables, mark the whole receipt (PO/shipment/location) with status = ‘TAX ERROR’

TABLE: AP_INVOICES_INTERFACE

Column	Value
INVOICE_ID	ap_invoices_interface_s.NEXTVAL
VENDOR_ID	Derived from VENDOR_SITE_CODE
VENDOR_NUM	Derived from VENDOR_SITE_CODE
VENDOR_SITE_ID	Derived from VENDOR_SITE_CODE
INVOICE_NUM	First 7 bytes of PO number||first 10 bytes of Shipment_num (ASN number)||ship_to_location (all from xxhbc_receipts table).  Off_po adjustments have this concatenated with AD# where # is the adjustment sequence.  Note Invoice Number can not have spaces.
INVOICE_AMOUNT	Total of detail amounts
EXCHANGE_RATE_TYPE	SELECT conversion_type  FROM gl_daily_conversion_types   WHERE user_conversion_type = 'IMPORT' and rownum = 1;
EXCHANGE_DATE	Use procedure APPS.xxhbcciu_ap_interface .populate_exchange_date
SOURCE	‘RETEK ZELLERS’
GROUP_ID	This identified the whole interface file. All invoices on the file will have the same group_id.  Set to: ‘ERS’||CCYYMMDDHHMMSS (current sysdate)
VOUCHER_NUM	Set to: Group_id||’ ‘||invoice_num
TERMS_ID	Derive terms from Vendor site terms_id
INVOICE_RECEIVED_DATE	Xxhbc_receipts.creation_date
GL_DATE	Xxhbc_receipts.creation_date
ATTRIBUTE_CATEGORY	'Transaction Code'
ATTRIBUTE4 -- HBC AP Inv Transaction Code	401 (900 for off_po adjustments)
GOODS_RECEIVED_DATE	Xxhbc_receipts.goods_received_date
TERMS_DATE	Set to same value as GOODS_RECEIVED_DATE
DESCRIPTION	(Discount description)}||‘PO=ppppppppp ASN=aaaaaaaa  Loc=llllllllllll’ Where pppppppp= Xxhbc_receipts.po_number, aaaaaaa = Xxhbc_receipts.shipment_num, llllllll= Xxhbc_receipts.ship_to_location
LAST_UPDATED_BY	User
LAST_UPDATE_DATE	Sysdate
CREATION_DATE	Sysdate
CREATED_BY	User

TABLE:  AP_INVOICE_LINES_INTERFACE

Column	Value
INVOICE_ID	ap_invoices_interface_s.CURVAL
INVOICE_LINE_ID	ap_invoice_lines_interface_s.NEXTVAL
LINE_NUMBER	Seq starting from 1
LINE_TYPE_LOOKUP_CODE	‘ITEM’ or ‘TAX’ depending on line type
TAX_CODE	For ‘TAX’ lines, check 3way match code for logic on how to derive the tax code.
DIST_CODE_COMBINATION_ID	Derive from 6 segments on xxhbc_receipts
DIST_CODE_CONCATENATED
DESCRIPTION	‘PO=ppppppppp ASN=aaaaaaaa Loc=llllllllllll UPC/SKU=uuuuuuu/sssssss’ (get all values from Xxhbc_receipts recordl to help identify the invoice line)  UPC is the Ordering UPC from Retek SKU/UPC mapping table (apps.UPC_EAN). Where pppppppp= Xxhbc_receipts.po_number, aaaaaaa = Xxhbc_receipts.shipment_num, llllllll= Xxhbc_receipts.ship_to_location, sssssss=SKU
LAST_UPDATED_BY	User
LAST_UPDATE_DATE	Sysdate
CREATION_DATE	Sysdate
CREATED_BY	User
AMOUNT	Unit_price * quantity_received.  For TAX records derive based on Rate. Summarize taxes by type to make sure there is just one TAX line per type.
GLOBAL_ATTRIBUTE2	Quantity_invoiced
GLOBAL_ATTRIBUTE1	Unit_price

Payment remittance process ELECTRONIC (EDI820) – xxhbc_3way_edi820_pkg

Payment remittances will be sent out to vendors via EDI820 transactions on a daily basis.  This remittance will include all checks generated on that day.  The Concurrent Job will also allow users to run the request and specify if a check needs to be resent (from another day) to the vendor via EDI820.

This process describes ELECTRONIC EDI820 remittances.  Paper remittances are described later in the document.

*** THIS PROCESS IS A MODIFICATION TO EXISTING EDI820 PROCESS DONE FOR 3WAY VENDORS CURRENTLY ***

Modify the existing EDI820 program (xxhbc_3way_edi820_pkg) to accept a check number (USD or CAD) to be re-extracted to EDI820 file.  Make sure the check number is a valid issued check.  This means the CM job will allow this in ADDITION to the date parameter.  If a date is specified, extract ALL checks on that date.  If a check number is specified, only extract that check, regardless of the date it was created.

The extract will concatenate the date time stamp to the filename output.  This will allow multiple files of EDI820 per day.

EDI transactions are created based on the checks generated and the detail (invoices and adjustments) that make up that check. 

Each invoice on the check needs to have an A* entry or R* entries.  Not both. 

· The following invoices require R* EDI820 entries:

TranType (Attribute4) = 401 and source in (‘EDI 810’, ’RETEK ZELLERS’)

TranType (Attribute4) = 900 and source in (’RETEK ZELLERS’)

TranType (Attribute4) = 401 and source in (‘RETEK CONSIGNMENT BAY’, ‘RETEK CONSIGNMENT ZELLERS’)  *** Special EDI820 mapping based on Retek View for details***

· The rest of the invoices require just the A* EDI820 entry.

· Only create records where the vendor is marked as EDI820 remittance method of Electronic or Both.  Paper remittances are described later in the document and are produced as a report to be sent to vendors.

The hierarchy of the transactions is as follows:

H1 (one per Check)

D1 (one per Check)

--- A1 (Manual debits, Volume Rebates, etc) (One per Debit note) ALL invoices NOT part of the R1 selection

--- R1 (Merchandise invoices, Off-PO adjustments) (One per invoice/PO) ALL 401 (EDI 810 and RETEK ZELLERS) and 900 (RETEK ZELLERS) and Consignment invoices

-------- R2 (Invoice item details) (One per invoice line/SKU) ALL 401 ITEM lines and Consignment invoices ITEM lines

-------- R3 (non-merchandise items [e.g. Taxes] and merchandise adjustments)  (One per invoice line/SKU) ALL 401 TAX lines and ALL 900 lines and Consignment invoices TAX lines

-------- R4 (reason for adjustment) Based on 900 ITEM lines

-------- R5 (only if R3 record is a merchandise item adjustment, this record includes SKU item information) Based on 900 ITEM lines

Generally, if the invoice is Transaction code 401 (merchandise invoices Retek or EDI810) or 900 (off-po adjustments) the R* level transactions are required, for other adjustments A1 transaction is used.  If an invoice has detail lines (AP_INVOICE_DISTRIBUTIONS_ALL) with a PO_distribution_id, the R* transactions are needed, otherwise the A1 is required.

ERS invoices and adjustments will have details required for R* records. Consignment invoices will have different R1, R2 and R3 mappings from ERS and EDI810 invoices.

The R*and A1 transaction amounts should add up to the Check amount reported on H1 transaction.

The EDI process will be done in two stages because EDI requires a mainframe conversion of the signed number fields.  What follows is the detailed description of the Oracle interface of remittances. 

All number fields should be zero filled (at left) and if signed, include a leading space indicating the sign of the number (‘-‘ or ‘+’).  For amounts having dollars and cents, multiply by 100 to remove the decimal.

The mainframe record length of EDI820 records is 250 bytes.

ONLY create output for vendor sites where xxhbc_sites_all.EDI820_METHOD in (‘Electronic, ‘Both’)

Note:

The relationship between 3way MATCHED Invoice and PO is:

po_distributions_all.PO_distribution_id = AP_INVOICE_DISTRIBUTIONS_ALL.PO_distribution_id

H1 – EDI820 - Remittance Header Record (1 Record per Remittance Advice/Check)

Element	Attributes	Comments	Req	Source Value
Key Group
EDI Transaction Set Code	x(3) value '820'		Y
EDI Record Type Code	x(7) value 'H1     '		Y
EDI Client	x(10) value ‘THE BAY   ‘ ‘ZELLERS   ‘ ‘BAYSPEC   ‘		Y	‘ZELLERS   ‘
EDI Partner	x(6)	vendor nbr	Y	ap_checks_all.vendor_site_code
Check nbr	9(7)		Y	ap_checks_all.check_number
Application Record Type Code	x(1) value '0'		Y
Filler	9(18) value '0'		Y
Data Group
VICS Transaction Code	x(1) value ‘I’
Check Amount	9(9)v99	* 100 to remove decimals, zero fill		ap_checks.amount
VICS Credit Flag	x(1) value ‘C’
VICS Pmt Method Code	x(3) value ‘CHK’
VICS Pmt Format Code	x(3) value ‘PBC’
Check Date	9(8)	CCYYMMDD		ap_checks.check_date
VICS Check Qualifier	x(2) value ‘CK’

D1 – EDI820 - Remittance Vendor Detail Record (1 Record per Vendor Remittance Advice)

Element	Attributes	Comments	Req	Source Value
Key Group
EDI Transaction Set Code	x(3) value '820'		Y
EDI Record Type Code	x(7) value 'D1     '		Y
EDI Client	x(10) value ‘THE BAY   ‘ ‘ZELLERS   ‘ ‘BAYSPEC   ‘		Y	‘ZELLERS   ‘
EDI Partner	x(6)	Zellers vendor nbr	Y	ap_checks_all.vendor_site_code
Check nbr	9(7)		Y	ap_checks_all.check_number
Application Record Type Code	x(1) value '1'		Y
Assigned Id.	9(6)	Sequentially incrementing nbr. For each pmt adjustment (or remittance pmt). Starting with ‘1’	Y	‘1’ – since there will only be one of these per check
Filler	9(12) value '0'		Y
Data Group
Filler	x(198)			spaces

A1 – EDI820 - Payment Adjustment Detail Record (1 Record per non-PO Adjustment)

Invoice DOES NOT have detail lines (AP_INVOICE_DISTRIBUTIONS_ALL) with a PO_distribution_id

Invoice Transaction code (Attribute4) NOT = ‘900’ or ‘401’

Element	Attributes	Comments	Req	Source Value
Key Group
EDI Transaction Set Code	x(3) value '820'		Y
EDI Record Type Code	x(7) value 'A1     '		Y
EDI Client	x(10) value ‘THE BAY   ‘ ‘ZELLERS   ‘ ‘BAYSPEC   ‘		Y	‘ZELLERS   ‘
EDI Partner	x(6)	Zellers vendor nbr	Y	ap_checks_all.vendor_site_code
Check Nbr.	9(7)		Y	ap_checks_all.check_number
Application Record Type Code	x(1) value '2'		Y
Assigned ID	9(6)		Y	000001
Filler	9(12) value '0'		Y
Data Group
Payment Adjustment Amount	9(9)v99			Abs(ap_invoices_all.paid_amount)
VICS Adjustment Reason Code	x(2)			‘ZZ’
Adjustment Adx Ref Qual Code	x(2)			If ap_invoices_all.paid_amount < 0 then = ‘DB’ else = ‘CM’
Adjustment Adx Ref ID  Nbr.	x(25)	Invoice #		ap_invoices_all.invoice_number
Debit-Credit Ref Qual. Code #1	x(2) value ‘CM’ or ‘DB’	CM=credit memo DB=debit memo		If ap_invoices_all.paid_amount < 0 then = ‘DB’ else = ‘CM’
Debit-Credit Ref Text #1	x(30)			ap_invoices_all.source || first part of  ap_invoices_all.description
VICS Adjustment Date Qual.	x(3) value ‘097’
Adjustment Transaction Date	9(8)	CCYYMMDD		ap_invoices_all.invoice_date
Debit-Credit Ref Qual. Code #2	x(2) value ‘CM’ or ‘DB’			If ap_invoices_all.paid_amount < 0 then = ‘DB’ else = ‘CM’ (only need if ‘Debit-Credit Ref Text #2’ is NOT NULL, otherwise, set to null)
Debit-Credit Ref Text #2	X(30)			Remaining part of ap_invoices_all.description

R1 – EDI820 - Remittance Payment Detail Record (1 Record per remittance payment)

R* is needed ONLY if invoice has detail lines (AP_INVOICE_DISTRIBUTIONS_ALL) with a PO_distribution_id

OR ‘RETEK ZELLERS’

Invoices with Invoice Transaction code (Attribute4) = ‘900’ or ‘401’

Element		Attributes	Comments	Req		Source Value
Key Group
EDI Transaction Set Code		x(3) value '820'		Y
EDI Record Type Code		x(7) value 'R1     '		Y
EDI Client		x(10) value ‘THE BAY   ‘ ‘ZELLERS   ‘ ‘BAYSPEC   ‘		Y		‘ZELLERS   ‘
EDI Partner		x(6)	Zellers vendor nbr	Y		ap_checks_all.vendor_site_code
Check nbr		9(7)		Y		ap_checks_all.check_number
Application Record Type Code		x(1) value '4'		Y
Assigned Id Nbr		9(6)	sequentially incrementing number for each remittance payment (or payment adjustment) starting with 1.	Y
Filler		9(12) value '0'		Y
Data Group
VICS Vendor Invoice Qualifier		x(2) value 'IV'
Vendor Invoice Nbr.		x(25)				ap_invoices_all.invoice_number
VICS Payment Action Code		x(2) value 'ER' or ‘PO’	ER = evaluated receipts PO = Payment on Account			‘PO’
Amount Paid		S9(9)v99				ap_invoices_all.Amount_paid
Total Credit-Debit Amt.		S9(9)v99				ap_invoices_all.Invoice_amount
Discount Amount		S9(9)v99				ap_invoices_all.Discount_amount_taken
VICS Ship Id. Qualifier		x(2) value ‘SI’ or ‘BM’				‘SI’ for ASN vendor, ‘BM’ for BOL vendor (Note only 3way vendors use BOL, ERS always uses ASN)
ASN Number		x(10)				FOR non-BOL vendor, derive shipment number from invoice description (Note only 3way vendors use BOL, ERS always uses ASN)
VICS Ship Id. Qualifier		x(2) value ‘SI’
ASN Number		x(10)				Derive from Invoice description
VICS Remittance Date Qualifier		x(3) value ‘097’
Remittance Date		9(8)	CCYYMMDD			ap_invoices_all.invoice_date
VICS PO Id. Qualifier		x(2) value ‘PO’ or ‘ST				‘PO’
PO Number / Store #		x(11)	Store used for Sbt			Derive PO Number from Invoice description
REF BOL #	x(20)				FOR BOL vendor: Use po_line_locations_all.shipment_num and the site number to look up the original ASN on XXHBC_3WAY_PO_SHIP_XREF table. (Note only 3way vendors use BOL, ERS always uses ASN)

R2 – EDI820 - Remittance Payment Item Record (1 Record per Item, per Remittance Payment)

One record for every line_type_lookup_code=‘ITEM’ and quantity_invoiced > 0 on the AP_INVOICE_DISTRIBUTIONS_ALL for the invoice

 (This is the original invoice information; all the adjustments will be on separate records)

line_type_lookup_code=‘ITEM’ and Invoice Transaction code (Attribute4) =‘401’

Element	Attributes	Comments	Req	Source Value
Key Group
EDI Transaction Set Code	x(3) value '820'		Y
EDI Record Type Code	x(7) value 'R2     '		Y
EDI Client	x(10) value ‘THE BAY   ‘ ‘ZELLERS   ‘ ‘BAYSPEC   ‘		Y	‘ZELLERS   ‘
EDI Partner	x(6)	Zellers vendor nbr	Y	ap_checks_all.vendor_site_code
Check nbr	9(7)		Y	ap_checks_all.check_number
Application Record Type Code	x(1) value '5'		Y
Assigned Id	same as on R1 record		Y
Filler	9(6) value '0'		Y
Item Assigned Id.	9(6)	sequentially incrementing nbr. for each item within a remittance pmt. starting at ‘1’	Y
Data Group
Quantity Invoiced	S9(7)		Y	AP_INVOICE_DISTRIBUTIONS_ALL. GLOBAL_ATTRIBUTE2
VICS Unit of Measure	x(4)	e.g. each	Y	‘EACH’
Unit Price	9(9)v99			AP_INVOICE_DISTRIBUTIONS_ALL. GLOBAL_ATTRIBUTE1
VICS UPC/EAN Qualifier	x(2) value ‘UP’ or ‘EN’	use UP for UPC, use EN for EAN		‘UP’
UPC or EAN Nbr	x(13)	when a UPC nbr. is specified, then a trailing space should appear in the right-most position		Derive from AP_INVOICE_DISTRIBUTIONS_ALL.description
VICS SKU Qualifier	x(2) value ‘IN’
SKU Number	9(8)			Derive from Invoice distribution description
VICS Style Qualifier	x(2) value ‘IT’
Style Number	x(20)
VICS Colour Qualifier	x(2) value ‘BO’
Colour Description	x(10)
VICS Size Code	x(2) value ‘IZ’
Size Description	x(10)

R3 – EDI820 - Remittance Payment Adjustment Record

(These are adjustments to the vendor invoice [line_type_lookup_code=‘ITEM’ and quantity_invoiced < 0] and non-ITEM lines)

(line_type_lookup_code=‘TAX’ and Invoice Transaction code (Attribute4) =‘401’)

AND (all  Invoice Transaction code (Attribute4) =‘900’  lines)

Element	Attributes	Comments	Req	Source Value
Key Group
EDI Transaction Set Code	x(3) value '820'		Y
EDI Record Type Code	x(7) value 'R3     '		Y
EDI Client	x(10) value ‘THE BAY   ‘ ‘ZELLERS   ‘ ‘BAYSPEC   ‘		Y	‘ZELLERS   ‘
EDI Partner	x(6)	Zellers vendor nbr	Y	ap_checks_all.vendor_site_code
Check nbr	9(7)		Y	ap_checks_all.check_number
Application Record Type Code	x(1) value '6'		Y
Assigned Id	9(6)	same as in R1 record	Y
Remittance Adjustment Assigned Id	9(6) value ‘1’	sequentially incrementing nbr. for each adjustment within a remittance pmt. starting at ‘1’	Y
Filler	9(6) value ‘0’		Y
Data Group
Remittance Adjustment Amount	S9(9)v99			AP_INVOICE_DISTRIBUTIONS_ALL.amount
X12 Adjustment Reason Code	x(2)	‘BA’ Gst ‘BB’ Qst ‘BC’ Hst ‘L2’  Discount ‘L3’  Penalty or           Charged ‘L7’ Credit quantity desc on Asn > actual qty ‘L8’ Debit quantity desc on Asn < actual qty		For tax lines, select the correct tax type. (use AP_TAX_CODES_ALL table to link the CCIDs to determine tax type).   Most times will = ‘L3’

R4 – EDI820 - Remittance Payment Adjustment Notes Record

This has the item information for the adjusted item (only for matched po_line_locations_all items)

(line_type_lookup_code=‘ITEM’ and quantity_invoiced < 0)

line_type_lookup_code=‘TAX’ and Invoice Transaction code (Attribute4) = ‘900’

Element	Attributes	Comments	Req	Source Value
Key Group
EDI Transaction Set Code	x(3) value '820'		Y
EDI Record Type Code	x(7) value 'R4     '		Y
EDI Client	x(10) value ‘THE BAY   ‘ ‘ZELLERS   ‘ ‘BAYSPEC   ‘		Y	‘ZELLERS   ‘
EDI Partner	x(6)	Zellers vendor nbr	Y	ap_checks_all.vendor_site_code
Check nbr	9(7)		Y	ap_checks_all.check_number
Application Record Type Code	x(1) value '7'		Y
Assigned Id	9(6)	same as R1 record	Y
Remittance Adjustment Assigned Id	9(6)	same as R3 record	Y
Filler	9(6) value ‘0’		Y
Data Group
Remittance Adjustment Description	x(60)			AP_INVOICE_DISTRIBUTIONS_ALL.description

R5 – EDI820 - Remittance Payment Adjustment Item Record (1 Record per Item, per Remittance Adjustment, per Remittance Pmt)

This is the detail to support R4 record

line_type_lookup_code=‘ITEM’ and Invoice Transaction code (Attribute4) = ‘900’

Element	Attributes	Comments	Req	Source Value
Key Group
EDI Transaction Set Code	x(3) value '820'		Y
EDI Record Type Code	x(7) value 'R5     '		Y
EDI Client	x(10) value ‘THE BAY   ‘ ‘ZELLERS   ‘ ‘BAYSPEC   ‘		Y	‘ZELLERS   ‘
EDI Partner	x(6)	Zellers vendor nbr	Y	ap_checks_all.vendor_site_code
Check nbr	9(7)		Y	ap_checks_all.check_number
Application Record Type Code	x(1) value '8'		Y
Assigned Id	9(6)	same as R1	Y
Remittance Adjustment Assigned Id	9(6)	same as R3	Y
Item Assigned Id.	9(6) value ‘1’	sequentially incrementing nbr. for each item within an adjustment for a remittance pmt. starting at ‘1’	Y
Data Group
Quantity Invoiced	S9(7)		Y	AP_INVOICE_DISTRIBUTIONS_ALL. GLOBAL_ATTRIBUTE2
VICS Unit of Measure	x(4)	e.g. each	Y	‘EACH’
Unit Price	9(9)v99			AP_INVOICE_DISTRIBUTIONS_ALL. GLOBAL_ATTRIBUTE1
VICS UPC/EAN Qualifier	x(2) value ‘UP’ or ‘EN’	use UP for UPC, use EN for EAN		‘UP’
UPC or EAN Nbr	x(13)	when a UPC nbr. is specified, then a trailing space should appear in the right-most position		Derive from AP_INVOICE_DISTRIBUTIONS_ALL.description
VICS SKU Qualifier	x(2) value ‘IN’
SKU Number	9(8)			Get SKU number from Invoice detail description
VICS Style Qualifier	x(2) value ‘IT’
Style Number	x(20)
VICS Colour Qualifier	x(2) value ‘BO’
Colour Description	x(10)
VICS Size Code	x(2) value ‘IZ’
Size Description	x(10)

Consignment invoice EDI820 mapping

All Consignment invoices will have EDI820 records.  These invoices have source = ‘RETEK CONSIGNMENT BAY’ or ‘RETEK CONSIGNMENT ZELLERS’.  EDI820 H1 and D1 mapping is the same as above since these are Check level records.  R1, R2, R3 mapping is described below.  Consignment invoices will NOT have A1, R4 and R5 records.

When a check contains any invoices with source = ‘RETEK CONSIGNMENT BAY’ or ‘RETEK CONSIGNMENT ZELLERS’, use the below field mapping for generating the R1,2,3 records for these invoices.

A database link will be used to get the data from Retek Tables for the data.

Bay invoice details are in views: CONSIGN_820_REMIT_HDR_RTKBRO_V (Header) and CONSIGN_820_REMIT_DTL_RTKBRO_V (Detail)

Zellers invoice details are in views: CONSIGN_820_REMIT_HDR_V (Header) and CONSIGN_820_REMIT_DETAIL_V  (Detail).

Join by SUPPLIER (Site number), CHECK_NO (Check number), INVOICE_NO (Invoice number).  Check one view for the invoice, if not there, check the other.  If invoice is not on either view, get data from AP invoice record.

Use the views to get required invoice data based on the AP invoice in Oracle.  Most of the details will come from the views for EDI820 creation.

R1 – EDI820 - Remittance Payment Detail Record (1 Record per remittance payment) CONSIGNMENT

source = ‘RETEK CONSIGNMENT BAY’ or ‘RETEK CONSIGNMENT ZELLERS’

Element	Attributes	Comments	Req	Source Value
Key Group
EDI Transaction Set Code	x(3) value '820'		Y
EDI Record Type Code	x(7) value 'R1     '		Y
EDI Client	x(10) value ‘THE BAY   ‘ ‘ZELLERS   ‘ ‘BAYSPEC   ‘		Y	‘ZELLERS   ‘
EDI Partner	x(6)	Zellers vendor nbr	Y	ap_checks_all.vendor_site_code
Check nbr	9(7)		Y	ap_checks_all.check_number
Application Record Type Code	x(1) value '4'		Y
Assigned Id Nbr	9(6)	sequentially incrementing number for each remittance payment (or payment adjustment) starting with 1.	Y
Filler	9(12) value '0'		Y
Data Group
VICS Vendor Invoice Qualifier	x(2) value 'IV'
Vendor Invoice Nbr.	x(25)			ap_invoices_all.invoice_number
VICS Payment Action Code	x(2) value 'ER' or ‘PO’	ER = evaluated receipts PO = Payment on Account		‘PO’
Amount Paid	S9(9)v99			ap_invoices_all.Amount_paid
Total Credit-Debit Amt.	S9(9)v99			ap_invoices_all.Invoice_amount
Discount Amount	S9(9)v99			ap_invoices_all.Discount_amount_taken
VICS Ship Id. Qualifier	x(2) value ‘SI’
ASN Number	x(10)
VICS Remittance Date Qualifier	x(3) value ‘097’
Remittance Date	9(8)	CCYYMMDD		ap_invoices_all.invoice_date
VICS PO Id. Qualifier	x(2) value ‘PO’ or ‘ST			‘PO’
PO Number / Store #	x(11)	Store used for Sbt		VIEW HEADER . STORE

R2 – EDI820 - Remittance Payment Item Record (1 Record per Item, per Remittance Payment) CONSIGNMENT

source = ‘RETEK CONSIGNMENT BAY’ or ‘RETEK CONSIGNMENT ZELLERS’ line_type_lookup_code=‘ITEM’

Element	Attributes	Comments	Req	Source Value
Key Group
EDI Transaction Set Code	x(3) value '820'		Y
EDI Record Type Code	x(7) value 'R2     '		Y
EDI Client	x(10) value ‘THE BAY   ‘ ‘ZELLERS   ‘ ‘BAYSPEC   ‘		Y	‘ZELLERS   ‘
EDI Partner	x(6)	Zellers vendor nbr	Y	ap_checks_all.vendor_site_code
Check nbr	9(7)		Y	ap_checks_all.check_number
Application Record Type Code	x(1) value '5'		Y
Assigned Id	same as on R1 record		Y
Filler	9(6) value '0'		Y
Item Assigned Id.	9(6)	sequentially incrementing nbr. for each item within a remittance pmt. starting at ‘1’	Y
Data Group
Quantity Invoiced	S9(7)		Y	VIEW DETAIL. UNITS
VICS Unit of Measure	x(4)	e.g. each	Y	‘EACH’
Unit Price	9(9)v99			VIEW DETAIL. UNIT_COST
VICS UPC/EAN Qualifier	x(2) value ‘UP’ or ‘EN’	use UP for UPC, use EN for EAN		‘UP’
UPC or EAN Nbr	x(13)	when a UPC nbr. is specified, then a trailing space should appear in the right-most position		VIEW DETAIL. UPC
VICS SKU Qualifier	x(2) value ‘IN’
SKU Number	9(8)			VIEW DETAIL. SKU
VICS Style Qualifier	x(2) value ‘IT’
Style Number	x(20)
VICS Colour Qualifier	x(2) value ‘BO’			VIEW DETAIL. COLOUR_DESC
Colour Description	x(10)
VICS Size Code	x(2) value ‘IZ’			VIEW DETAIL. SIZE_DESC
Size Description	x(10)

R3 – EDI820 - Remittance Payment Adjustment Record CONSIGNMENT

source = ‘RETEK CONSIGNMENT BAY’ or ‘RETEK CONSIGNMENT ZELLERS’ line_type_lookup_code=‘TAX’

Element	Attributes	Comments	Req	Source Value
Key Group
EDI Transaction Set Code	x(3) value '820'		Y
EDI Record Type Code	x(7) value 'R3     '		Y
EDI Client	x(10) value ‘THE BAY   ‘ ‘ZELLERS   ‘ ‘BAYSPEC   ‘		Y	‘ZELLERS   ‘
EDI Partner	x(6)	Zellers vendor nbr	Y	ap_checks_all.vendor_site_code
Check nbr	9(7)		Y	ap_checks_all.check_number
Application Record Type Code	x(1) value '6'		Y
Assigned Id	9(6)	same as in R1 record	Y
Remittance Adjustment Assigned Id	9(6) value ‘1’	sequentially incrementing nbr. for each adjustment within a remittance pmt. starting at ‘1’	Y
Filler	9(6) value ‘0’		Y
Data Group
Remittance Adjustment Amount	S9(9)v99			AP_INVOICE_DISTRIBUTIONS_ALL.amount
X12 Adjustment Reason Code	x(2)	‘BA’ Gst ‘BB’ Qst ‘BC’ Hst ‘L2’  Discount ‘L3’  Penalty or           Charged ‘L7’ Credit quantity desc on Asn > actual qty ‘L8’ Debit quantity desc on Asn < actual qty		For tax lines, select the correct tax type. (use AP_TAX_CODES_ALL table to link the CCIDs to determine tax type).   Most times will = ‘L3’

.

When to Run the Program

Daily

Launch Parameters

CM request – XXHBC 3 Payment Remittance outbound EDI820

Parameters :

Date

Check Number

Restart Procedures

TBD

Payment remittance process PAPER (EDI820) – xxhbc_edi820_remittance

Some vendors can not receive Electronic EDI820 remittances or would like to receive paper EDI820 data as well as electronic.  These vendors are marked xxhbc_sites_all.EDI820_METHOD  ‘Paper’ or ‘Both’ .  For these vendors we need to create a report to send to them by mail.

HUDSON’S BAY                                         Cheque # F1

820 REMITANCE ADVICE                                       Date : F23

Paid To:                                                                                                                                                                Page : F2

F3

F4

F5

F6           F8                                                                                                                                           Vendor Site # F10

F7           F9

DOCUMENT #  Description         PO #      Date       UPC/SKU                            QTY Rec                               Unit Price                             Total

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------            

F11                         F12                         F13         F14         F15                         F16                         F17                         F18

                                                                                                                                                                Subtotal                                F19

                                                                                                                                                                GST/ HST                           F20

                                                                                                                                                                QST                        F21

                                                                                                                                                                Total Before Terms            F22

                                                                                                                                                                                ===================

-          Page break on Vendor Site number and Check number

-          Get details as on electronic EDI820 remittance

-          Document number is Invoice Number.  Repeat more than one document on a page if exists on the cheque.

-          D12 is invoice header description

-          D3-9 is vendor pay to address

-          On each new vendor, first page is to contain ONLY the vendor address as shown in scan.

-          See scan of current report for clarification of data and layout.  This report is similar to this

When to Run the Program

Daily and on request

Launch Parameters

CM request – XXHBC Payment Remittance outbound EDI820 PAPER

Parameters :

Date

OR

Check Number

Restart Procedures

TBD

Open and Closed Issues for this Deliverable

Open Issues

ID	Issue	Resolution	Responsibility	Target Date	Impact Date

Closed Issues

ID	Issue	Resolution	Responsibility	Target Date	Impact Date

 CREATE OR REPLACE PACKAGE APPS.xxhbc_3way_all_receipt
AS
/* -------------------------------------------------------------------------- */
/*  Program Name : XXHBC_3WAY_ALL_RECEIPT                         */
/*                                                                            */
/*  TYPE         : PL/SQL Package                                             */
/*                                                                            */
/*  Input Parms  :                                                */
/*                                                                            */
/*  Output Parms : --                                                         */
/*                                                                            */
/*  Table Access : -- xxhbc_3way_po_ship_iface,xxhbc_sites_all                  */
/*                                                                            */
/*  AUTHOR       : Chandra Sekhar Kadali                                      */
/*                                                                            */
/*  DATE         : 11-JAN-2013                                                */
/*                                                                            */
/*  VERSION      : 1.0                                                        */
/*                                                                            */
/*  DESCRIPTION  : XXHBC_3WAY_ALL_RECEIPTconcurrent program is used*/
/*  to process all receipts from Retek on a daily base                       */
/*                                                                            */
/*                                                                            */
/*  CHANGE HISTORY                                                            */
/* -------------------------------------------------------------------------- */
/* DATE        AUTHOR                          VERSION  REASON                */
/* -------------------------------------------------------------------------- */
/* 17/01/2013  Chandra Sskhar  Kadali          1.0      Initial creation      */
/* -------------------------------------------------------------------------- */
   PROCEDURE main(
      errbuf                     OUT      VARCHAR2
     ,retcode                    OUT      VARCHAR2);

   FUNCTION get_vendor_site_id(
      p_vendor_site_code                  VARCHAR2)
      RETURN NUMBER;

   FUNCTION get_vendor_id(
      p_vendor_site_code                  VARCHAR2)
      RETURN NUMBER;

   FUNCTION get_vendor_num(
      p_vendor_site_code                  VARCHAR2)
      RETURN VARCHAR2;

   FUNCTION get_terms_id(
      p_vendor_site_code                  VARCHAR2)
      RETURN NUMBER;

   FUNCTION get_exchange_rate_type
      RETURN VARCHAR2;

   FUNCTION get_dist_code_combination_id(
      p_segment1                          VARCHAR2
     ,p_segment2                          VARCHAR2
     ,p_segment3                          VARCHAR2
     ,p_segment4                          VARCHAR2
     ,p_segment5                          VARCHAR2
     ,p_segment6                          VARCHAR2)
      RETURN NUMBER;

   FUNCTION get_tax_code_concatenated(
      p_tax_ccid                          NUMBER)
      RETURN VARCHAR2;
      PROCEDURE submit_import(
      p_source                   IN       VARCHAR2
     --,p_group_id   IN VARCHAR2
   ,  p_batch_date               IN       VARCHAR2
   , p_request_id out number                                            
   );
END xxhbc_3way_all_receipt;
/