LOGISTICS INFORMATION ACCESS
Detailed Description of the Program

Objectives

1. Migrate the existing Logistics Information Access from an .asp web application to a Microsoft SharePoint 2007 operating platform. 2. Shorten the process flow by eliminating repeating and irrelevant processes and information. 3. Synchronize the flow of information within the processes involved. 4. Provide alerts every time there is a new form created as well as whenever there are some changes with the information in the form. 5. Automatically generate reports.

Process Flow

[pic]

[pic]

Assumptions:

• Logistics Information Access forms are initially created every time there is a new purchase order. • Request for Freight forms are initially created every time a buyer receives a quotation from the suppliers • Each purchase order may contain single or several job orders and work orders. • Normal shipments are those purchase orders which contain a single job order or work order. On the other hand, purchase orders which have several job orders or work orders are referred as consolidated shipments. • The number of work order is equal to the number of job order for a certain purchase order. • Work order numbers will be automatically generated for the request for freight form on the basis of the current month and year. • Duties and Taxes information can only be inputted to those purchase orders which have job orders. • Abridged Contract and Freight Requirement Report can only be created if an Award Endorsement has been created. • Access to the forms may vary per user depending upon the allowed information and tasks handled by each user.

Users

People who have accessed to LIA are classified into groups depending upon the tasks that they need to perform and more importantly on the information that they are allowed to access.

• PETHUB_LIA_ADMINISTRATORS They have the full access to all the forms and reports in LIA. They also have the responsibility of managing the lists for the drop down boxes in all the forms in cases wherein they have to add, edit or delete any item in the lists.

• PETHUB_MSPD_BUYERS Their access to LIA is limited only in filling out the Request for Freight form. Thus, they don’t have the authorization to access the other form and report functionalities of LIA.

• PETHUB_LIA_MEMBERS Similar to the pethub_mspd_buyers, they are only allowed to access a portion of the LIA particularly the Logistics Information Access form. However, they are only limited to the viewing of the said form.

Program Specifications

I. Request For Freight

a. Data Connections

|Connection Name |Description |
|Main Submit |Submits data to the SharePoint library (Request for |
| |Freight) upon saving of the form (File name - WO_No) |
|Shipper |Receives data from the Shipper list at the SharePoint |
| |site |
|Local Supplier |Receives data from the Local Supplier list at the |
| |SharePoint site |
|Freight Status |Receives data from the Freight Status list at the |
| |SharePoint site |
|Currency |Receives data from the Currency list at the SharePoint |
| |site |
|Option |Receives data from the Option list at the SharePoint |
| |site |
|Type of Container |Receives data from the Type of Container list at the |
| |SharePoint site |
|Weight |Receives data from the Weight list at the SharePoint |
| |site |
|GetUserProfileByName |Web Service Connection getting the current user logged |
| |in to the computer |
|Request for Freight |Receives data in the Request for Freight form library |
| |at the SharePoint site |

b. Rules upon Opening of the Form

Rule 1: • User’s current role is equal to ‘Buyers’ (PETHUB_MSPD_BUYERS) Actions: • Switch to View to ‘Buyer’s View’

Rule 2: • User’s current role is equal to ‘Full Access’ (PETHUB_LIA_ADMINISTRATORS, petnet\etdungo, petnet\jcmarasigan) Actions: • Switch to View ‘View 1’

Rule 3: • User’s current role is not equal to ‘Full Access’ and User’s current role is not equal to ‘Buyers’ Actions: • Switch to View to ‘Blank View’ • Show a dialog box message : You are not allowed to view this form

c. Buyer’s View of the Request for Freight Form

Upon receiving of quotations from suppliers, buyers (PETHUB_MSPD_BUYERS) fill out the form which includes the following fields:

|Field Name |Data Type |Default Value |
|WO_No |Text (string) |concat("PC", month, year, "-", counter) |
|PO_No |Text (string) | |
|PR_No |Text (string) | |
|Description of Goods |Text (string) | |
|Terms (FOB Port of Exit) |Text (string) |FOB Port of Exit |
|Terms (Ex - Factory) |Text (string) |Ex - Factory |
|Port |Text (string) | |
|Additional_Packing_Information |Text (string) | |
|Pickup_Company |Text (string) | |
|Pickup_Company_Contact_Person |Text (string) | |
|Postal_Code |Text (string) | |
|Pickup_Company_Address |Text (string) | |
|Pickup_Company_Email |Text (string) | |
|Pickup_Company_TelFax_No |Text (string) | |
|Final_Destination |Text (string) | |
|Contact_Person |Text (string) | |
|Estimated_Date_of_Shipment |Date (date) | |
|Date_Wanted_in_Field |Date (date) | |
|Currency_Estimated_FreightCost (Drop Down) |Text (string) |(Currency - Secondary Data Source) @Currency |
|Estimated_Freight_Cost |Decimal (double) | |
|Shipper1 (Drop Down) |Text (string) |(Shipper - Secondary Data Source) @Shipper |
|Shipper2 |Text (string) | |
|Shipper_Address |Text (string) | |
|Shipper_Contact_Person |Text (string) | |
|Shipper_TelFax_Number |Text (string) | |
|Shipper_Email |Text (string) | |
|Local_Supplier1 (Drop Down) |Text (string) |(Local Supplier - Secondary Data Source) @Local |
| | |Supplier |
|Local_Supplier2 |Text (string) | |
|Local_Supplier_Address |Text (string) | |
|Local_Supplier_Contact_Person |Text (string) | |
|Local_Supplier_TelFax_Number |Text (string) | |
|Local_Supplier_Email |Text (string) | |
|Mode_of_Shipment (Air) |Text (string) |Air |
|Mode_of_Shipment (Sea) |Text (string) |Sea |
|Mode_of_Shipment_To_Follow |True/False(boolean) |Cleared (False) |
|Mode_of_Packing (Loose Cargo) |Text (string) |Loose Cargo |
|Mode_of_Packing (Containerized) |Text (string) |Containerized |
|Dimension |Text (string) | |
|Dimension_To_Follow |True/False(boolean) |Cleared (False) |
|Weight_of_Package |Decimal (double) | |
|Weight_of_Package_Unit (Drop Down) |Text (string) |(Weight - Secondary Data Source) @Weight |
|No_of_Container |Whole Number (integer) | |
|Type of Container (Drop Down) |Text (string) |(Type of Container - Secondary Data Source) |
| | |@Type of Container |
|Container_Cargo_Remarks |Text (string) | |
|Additional_Information |Text (string) | |
|Rush_Emergency_Shipment |True/False(boolean) |Cleared (False) |
|Requested_by |Text (string) |Value[Name = "PreferredName"] |
|Date_Requested |Date (date) |today() |

Functions Involve in the Buyer’s View of the Request for Freight Form

A. Generation of Work Order Numbers: Upon opening of the form, the Work Order Numbers will be automatically generated depending upon the current month and year

Hidden fields created in order to perform the function include the following:

|Field Name |Data Type |Default Value |
|counter |Text (string) | |
|month |Text (string) |substring(today(), 6, 2) |
|year |Text (string) |substring(today(), 3, 2) |
|monthyear |Text (string) |concat(month, year) |
|ctr |Text (string) | |
|validate |Text (string) | |
|get |Text (string) |monthyear |

1. The get field will trigger the start of the function by performing the different rules set in the given field.

Rule 1: This checks whether the current month and year are already found in the library. • ‘get’ is not blank Actions: • Set a field’s value : i. Validate: contains (@monthyear[. = monthyear], monthyear)

Rule 2: If the current month and year are not found in the library, it means that this is the first work order for the given month and year which will set the ctr and counter fields with the value of 1.

• ‘validate’ is equal to false Actions: • Set a field’s value i. ctr :1 ii. counter:1

Rule 3: If the current month and year are already found in the library, this means that there is already a work order present for the given month and year. Thus, the work order number that will be generated will be based on the maximum work order number for the current month and year plus one.

• ‘validate’ is equal to true Actions: • Set a field’s value i. ctr: max(@ctr[monthyear = @monthyear]) + 1 ii. counter : ctr

B. Setting of the Request for Freight Status There are two statuses given for the request for freight created. The first one is the Pending status which is determined if the information entered are not complete. On the other hand, the Approved status is determined if buyer inputs a complete information about the given work order.

Hidden fields created in order to perform the function include the following:

|Field Name |Data Type |Default Value |
|formStatus |Text (string) | “Pending” |

Rule 1: This checks whether the shipping details / info are complete. Every fields involved (Mode_of_Shipment, Mode_of_Packing, Dimension, Weight_of_Package, Weight_of_Package_Unit, No_of_Container, Type_of_Container) are checked whether it is not blank. Actions: • Set a field’s value i. formStatus: “Approved”

Rule 2: This checks whether any shipping details / info is missing. Every fields involved (Mode_of_Shipment, Mode_of_Packing, Dimension, Weight_of_Package, Weight_of_Package_Unit, No_of_Container, Type_of_Container) are checked whether it is blank. Actions: • Set a field’s value i. formStatus: “Pending”

Once the buyer filled out the form, the entered information will now then be saved in the Request for Freight Library and will be sent to racruz@petron.com once the status of the Form has been approved.

d. Full Access View of the Form (View 1)

Once the request for freight form has been approved, any of the pethub_lia_administrators members will fill out the additional fields which include the following:

|Field Name |Data Type |Default Value |
|WO_No |Text (string) |concat("PC", month, year, "-", counter) |
|PO_No |Text (string) | |
|PR_No |Text (string) | |
|Description of Goods |Text (string) | |
|Terms (FOB Port of Exit) |Text (string) |FOB Port of Exit |
|Terms (Ex - Factory) |Text (string) |Ex - Factory |
|Port |Text (string) | |
|Additional_Packing_Information |Text (string) | |
|Pickup_Company |Text (string) | |
|Pickup_Company_Contact_Person |Text (string) | |
|Postal_Code |Text (string) | |
|Pickup_Company_Address |Text (string) | |
|Pickup_Company_Email |Text (string) | |
|Pickup_Company_TelFax_No |Text (string) | |
|Final_Destination |Text (string) | |
|Contact_Person |Text (string) | |
|Estimated_Date_of_Shipment |Date (date) | |
|Date_Wanted_in_Field |Date (date) | |
|Currency_Estimated_FreightCost (Drop Down) |Text (string) |(Currency - Secondary Data Source) @Currency |
|Estimated_Freight_Cost |Decimal (double) | |
|Shipper1 (Drop Down) |Text (string) |(Shipper - Secondary Data Source) @Shipper |
|Shipper2 |Text (string) | |
|Shipper_Address |Text (string) | |
|Shipper_Contact_Person |Text (string) | |
|Shipper_TelFax_Number |Text (string) | |
|Shipper_Email |Text (string) | |
|Local_Supplier1 (Drop Down) |Text (string) |(Local Supplier - Secondary Data Source) @Local |
| | |Supplier |
|Local_Supplier2 |Text (string) | |
|Local_Supplier_Address |Text (string) | |
|Local_Supplier_Contact_Person |Text (string) | |
|Local_Supplier_TelFax_Number |Text (string) | |
|Local_Supplier_Email |Text (string) | |
|Mode_of_Shipment (Air) |Text (string) |Air |
|Mode_of_Shipment (Sea) |Text (string) |Sea |
|Mode_of_Shipment_To_Follow |True/False(boolean) |Cleared (False) |
|Mode_of_Packing (Loose Cargo) |Text (string) |Loose Cargo |
|Mode_of_Packing (Containerized) |Text (string) |Containerized |
|Dimension |Text (string) | |
|Dimension_To_Follow |True/False(boolean) |Cleared (False) |
|Weight_of_Package |Decimal (double) | |
|Weight_of_Package_Unit (Drop Down) |Text (string) |(Weight - Secondary Data Source) @Weight |
|No_of_Container |Whole Number (integer) | |
|Type of Container (Drop Down) |Text (string) |(Type of Container - Secondary Data Source) |
| | |@Type of Container |
|Container_Cargo_Remarks |Text (string) | |
|Additional_Information |Text (string) | |
|Rush_Emergency_Shipment |True/False(boolean) |Cleared (False) |
|Requested_by |Text (string) |Value[Name = "PreferredName"] |
|Date_Requested |Date (date) |today() |
|Forwarder |Text (string) | |
|Availability_Date_of_Item |Date (date) | |
|Duration_of_Pickup |Text (string) | |
|Awaiting_Vessel_Plane |Text (string) | |
|Transit_Time |Text (string) | |
|Bid_Deadline |Date (date) | |
|Payment_Date |Date (date) | |
|Processed (Drop Down) |Text (string) |(Option - Secondary Data Source) @Option |
|Paid (Drop Down) |Text (string) |(Option - Secondary Data Source) @Option |
|NTP (Drop Down) |Text (string) |(Option - Secondary Data Source) @Option |
|Contract (Drop Down) |Text (string) |(Option - Secondary Data Source) @Option |
|Company_Estimate_Currency (Drop Down) |Text (string) |(Currency - Secondary Data Source) @Currency |
|Company_Estimate |Decimal (double) | |
|Contract_Price_Currency (Drop Down) |Text (string) |(Currency - Secondary Data Source) @Currency |
|Contract_Price |Decimal (double) | |
|Local_Charges |Decimal (double) | |
|Savings_Currency (Drop Down) |Text (string) |(Currency - Secondary Data Source) @Currency |
|Savings |Decimal (double) | |
|Actual_Payment_Currency (Drop Down) |Text (string) |(Currency - Secondary Data Source) @Currency |
|Actual_Payment |Decimal (double) | |
|Status (Drop Down) |Text (string) |(Freight Status - Secondary Data Source) |
| | |@Freight Status |
|ETD_EXW |Date (date) | |
|Status_Date |Date (date) |today() |
|ETA_Manila_Port |Date (date) | |
|Date_Awarded |Date (date) | |
|ETD_Port_of_Origin |Date (date) | |
|JO_No |Text (string) | |
|Tarriff_Code |Text (string) | |
|Material_No |Text (string) | |
|Tarriff_Rate |Text (string) | |
|Approved_Tarriff_Code |Text (string) | |
|Airway_Bill_No |Text (string) | |
|Actual_Date_of_Arrival |Date (date) | |
|Actual_Date_of_Release |Date (date) | |
|Delivery_Date |Date (date) | |
|Delivery_Charges |Decimal (double) | |
|Amount_of_DT_Paid |Decimal (double) | |
|Processing_Time |Text (string) | |
|Forwarding_Pickup_Company |Text (string) | |
|Loading_Add_1 |Text (string) | |
|Loading_Add_2 |Text (string) | |
|Loading_CP |Text (string) | |
|Contact_No |Text (string) | |
|Forwarding_Final_Destination |Text (string) | |
|Address |Text (string) | |

The functions that are applicable in the Full Access View are similar to those of the Buyer’s View

After inputting the added or updated information, approved request for freight report can now be generated containing all the data that are entered. This report will then be emailed to their specific forwarder.

II. Forwarding

a. Data Connections

|Connection Name | Description |
|Main Submit |Submits data to the SharePoint library (Forwarding) |
| |upon saving of the form (File name - WO_No) |
|Origin Charges |Receives data from the Origin Charges list at the |
| |SharePoint site |
|Local Charges |Receives data from the Local Charges list at the |
| |SharePoint site |
|Forwarders |Receives data from the Forwarders list at the |
| |SharePoint site |
|Request for Freight |Receives data in the Request for Freight form library |
| |at the SharePoint site |
|Forwarding |Receives data in the Forwarding form library at the |
| |SharePoint site |

b. Rules upon Opening of the Form

Rule 1: • User’s current role is equal to ‘Full Access’ (PETHUB_LIA_ADMINISTRATORS, petnet\etdungo, petnet\jcmarasigan) Actions: • Switch to View ‘Home’

Rule 2: • User’s current role is not equal to ‘Full Access’ Actions: • Switch to View to ‘Blank View’ • Show a dialog box message : You are not allowed to view this form

c. Award Endorsement View

Any of the pethub_lia_administrators members can now bid and then create an award for those approved request for freight which has a weight of more than 75 kilograms. To do so, the following fields should be filled out.

|Field Name |Data Type |Default Value |
|WO_No |Text (string) | |
|Exchange_Rate |Decimal (double) | |
|ER_Date |Date (date) | |
|Bid_Opening_Date |Date (date) | |
|Reason_for_Award |Text (string) | |
|Forwarder (Drop Down) (Master) |Text (string) |(Forwarder - Secondary Data Source) @Forwarder |
|Brokerage_Delivery_Charges (Detail) |Decimal (double) | |
|US_Discount (Detail) |Decimal (double) | |
|Peso_Discount (Detail) |Decimal (double) | |
|Origin_Charge_Name (Drop Down) (Detail) |Text (string) |(Origin Charges - Secondary Data Source) @Origin|
| | |Charges |
|Origin_US (Detail) |Decimal (double) | |
|Origin_Peso (Detail) |Decimal (double) | |
|Local_Charge_Name (Drop Down) (Detail) |Text (string) |(Local Charges - Secondary Data Source) @Local |
| | |Charges |
|Local_US (Detail) |Decimal (double) | |
|Local_Peso (Detail) |Decimal (double) | |
|US_Subtotal (Detail) |Decimal (double) |sum(Origin_US) + sum(Local_US) |
|Peso_Subtotal (Detail) |Decimal (double) |sum(Origin_Peso) + sum(Local_Peso) |

After completing the fields, the information will now then be saved in the Forwarding library and the Report for the Award Endorsement will then be generated.

Functions Involve in the Award Endorsement View

A. Checking if Entered Work Order is present in the Request for Freight Library and Forwarding Library Alerts are provided once the entered work order is not present in the request for freight library as well as on instances that the inputted work order has already an award endorsement created.

Hidden fields created in order to perform the function include the following:

|Field Name |Data Type |Default Value |
|exist |Text (string) | |
|exist2 |Text (string) | |
|forward |Whole Number(integer) | 0 |

Rule 1: This checks whether the entered work order exists in the request for freight and forwarding library • WO_No is not blank Actions: • Set a field’s value: i. exist: contains(@WO_No[. = WO_No], .) ii. exist2 : contains(@WO_No[. = WO_No], .)

Rule 2: This submits the work order number to the library once it has checked that the WO_No is present in the request for freight library but not present in the forwarding library. • exist is equal to “false” and exist2 is equal to “true” Actions: • Submit to a data connection : Main Submit • Set a field’s value: i. forward: 1 ii. exist : “true”

Data Validation Errors (Alerts): • forward is equal to 0 and exist is equal to “true” • forward is equal to 0 and exist2 is equal to “false”

B. Generation of the Report for Award Endorsement The Award Endorsement Report contains information about the forwarders that take their bid as well as their total charges for a specific work order. More so, this displays the forwarder who wins the bid in terms of the lowest cost.

Hidden fields created in order to perform the function include the following:

|Field Name |Data Type |Default Value |
|hold |Text (string) | |
|ctr |Whole Number (integer) | |
|Id (Detail) |Whole Number (integer) |0 |
|Substotals (Detail) |Decimal (double) |((round((((US_Subtotal - US_Discount) * |
| | |Exchange_Rate) + (Peso_Subtotal - |
| | |Peso_Discount)) * 100)) / 100) + |
| | |Brokerage_Delivery_Charges |
|holding2 |Whole Number (integer) | |
|holding |Text (string) | |
|idctr |Whole Number (integer) |0 |
|ctr2 |Whole Number (integer) | |
|holdlocal |Text (string) | |
|ctrlocal |Whole Number (integer) | |
|holding4 |Whole Number (integer) | |
|holding3 |Text (string) | |
|forward |Whole Number (integer) |0 |
|exist |Text (string) | |
|exist2 |Text (string) | |
|testing1 |Decimal (double) |substotals[id = 1] |
|testing2 |Decimal (double) |substotals[id = 2] |
|testing3 |Decimal (double) |substotals[id = 3] |
|testing4 |Decimal (double) |substotals[id = 4] |
|testing5 |Decimal (double) |substotals[id = 5] |
|testing6 |Decimal (double) |substotals[id = 6] |
|first |Decimal (double) |0 |
|second |Decimal (double) | |
|third |Decimal (double) | |
|forth |Decimal (double) | |
|fifth |Decimal (double) | |
|sixth |Decimal (double) | |
|id1 |Whole Number (integer) | |
|id2 |Whole Number (integer) | |
|id3 |Whole Number (integer) | |
|id4 |Whole Number (integer) | |
|id5 |Whole Number (integer) | |
|id6 |Whole Number (integer) | |
|FirstCharge |Text (string) |hold[ctr = 1] |
|SecondCharge |Text (string) |hold[ctr = 2] |
|ThirdCharge |Text (string) |hold[ctr = 3] |
|ForthCharge |Text (string) |hold[ctr = 4] |
|FifthCharge |Text (string) |hold[ctr = 5] |
|SixthCharge |Text (string) |hold[ctr = 6] |
|SeventhCharge |Text (string) |hold[ctr = 7] |
|EighthCharge |Text (string) |hold[ctr = 8] |
|NinthCharge |Text (string) |hold[ctr = 9] |
|TenthCharge |Text (string) |hold[ctr = 10] |
|FirstLocal |Text (string) |holdlocal[ctrlocal = 1] |
|SecondLocal |Text (string) |holdlocal[ctrlocal = 2] |
|ThirdLocal |Text (string) |holdlocal[ctrlocal = 3] |
|ForthLocal |Text (string) |holdlocal[ctrlocal = 4] |
|FifthLocal |Text (string) |holdlocal[ctrlocal = 5] |
|SixthLocal |Text (string) |holdlocal[ctrlocal = 6] |
|SeventhLocal |Text (string) |holdlocal[ctrlocal = 7] |
|EighthLocal |Text (string) |holdlocal[ctrlocal = 8] |
|NinthLocal |Text (string) |holdlocal[ctrlocal = 9] |
|TenthLocal |Text (string) |holdlocal[ctrlocal = 10] |
|EleventhLocal |Text (string) |holdlocal[ctrlocal = 11] |
|TwelfthLocal |Text (string) |holdlocal[ctrlocal = 12] |
|ThirteenthLocal |Text (string) |holdlocal[ctrlocal = 13] |
|ForteenthLocal |Text (string) |holdlocal[ctrlocal = 14] |
|FifteenthLocal |Text (string) |holdlocal[ctrlocal = 15] |
|SixteenthLocal |Text (string) |holdlocal[ctrlocal = 16] |
|EighteenthLocal |Text (string) |holdlocal[ctrlocal = 18] |
|NineteenthLocal |Text (string) |holdlocal[ctrlocal = 19] |
|TwentyLocal |Text (string) |holdlocal[ctrlocal = 20] |

Once the user inputs the information for a certain forwarder such as the Brokerage and Delivery Charges, US Discount, Peso Discount, Origin Charges and Local Charges, the totals for each will be automatically computed and then all the forwarders who bid will be sorted according to its cost and the forwarder with the lowest cost wins the bid.

In order to group the origin charges for all the forwarders, the following rules are done.

Origin_Charge_Name field Rule 1: This sets and checks whether the origin charge name entered has already been used for the forwarders that are entered previously • Origin_Charge_Name is not blank Actions: • Set a field’s value: i. Holding: @Origin_Charge_Name[@WO_No = WO_No]

Rule 2: This rule enumerates and sorts all the origin charges that have been added for all the forwarders. • Holding does not contain Origin_Charge_Name Actions: • Set a field’s value: i. hold = Origin_Charge_Name ii. holding2 = holding2 + 1 iii. ctr = holding2

In order to group the local charges for all the forwarders, the following rules are done.

Local_Charge_Name field Rule 1: This sets and checks whether the local charge name entered has already been used for the forwarders that are entered previously • Local_Charge_Name is not blank Actions: • Set a field’s value: ii. Holding3: @Local_Charge_Name[@WO_No = WO_No]

Rule 2: This rule enumerates and sorts all the local charges that have been added for all the forwarders. • Holding3 does not contain Local_Charge_Name Actions: • Set a field’s value: iv. holdlocal = Origin_Charge_Name v. holding4 = holding4 + 1 vi. ctrlocal = holding4

Once the charges have been grouped, the forwarders will now then be sorted from lowest cost to highest cost. To do so, the following rules are done:

Rule 1: The testing1, testing2, testing3, testing4, testing5 and testing6 hidden fields have default values that retrieve the totals for all the forwarders with a maximum of 6 forwarders Rule 2: Each of the field mentioned above (e.g. testing1) are compared with one another and checked whether they are less than or greater than the other. After which, the totals for all the forwarders are set to the hidden fields (first, second, third, forth, fifth, sixth) in which the lowest total is placed in the first field and the highest total is placed in the sixth field.

The ids for all the forwarders are retrieved and then sorted to the following fields:

|Field Name |Action Applied |
|id1 |id[substotals = first] |
|id2 |id[substotals = second] |
|id3 |id[substotals = third] |
|id4 |id[substotals = forth] |
|id5 |id[substotals = fifth] |
|id6 |id[substotals = sixth] |

Upon completion, the report for the award endorsement will be generated with the following outputs expected.

|Output Type |Formula Used |
|Reference |concat(@PR_No[WO_No = @WO_No], "/", @PO_No[WO_No |
| |= @WO_No]) |
|Item Description |@Description_of_Goods[WO_No = @WO_No] |
|Packaging |concat(@Weight_of_Package[WO_No = @WO_No], " ", |
| |@Weight_of_Package_Unit[WO_No = @WO_No]) |
|Origin |@Port[WO_No = @WO_No] |
|Supplier |@Shipper[WO_No = @WO_No] |
|Bid Opening |Bid_Opening_Date |
|Mode_of_Shipment |@Mode_of_Shipment[WO_No = @WO_No] |
|Incoterms |@Terms[WO_No = @WO_No] |
|first_forwarder |Forwarder[id = id1] |
|second_forwarder |Forwarder[id = id2] |
|third_forwarder |Forwarder[id = id3] |
|forth_forwarder |Forwarder[id = id4] |
|fifth_forwarder |Forwarder[id = id5] |
|sixth_forwarder |Forwarder[id = id6] |
|Recommend Award To |Forwarder[id = id1] |
|Reason for Award |Reason_for_Award |

For the display of the Origin Charges, Local Charges, Subtotals, Discount, Brokerage Delivery Charges and Totals; the following are just samples of the formula used.

|Output Type |Formula Used |
|Origin Charge |Origin_US[id = id1 and Origin_Charge_Name = |
| |FirstCharge] |
| |Origin_US[id = id2 and Origin_Charge_Name = |
| |FirstCharge] |
| |Origin_Peso[id = id1 and Origin_Charge_Name = |
| |SecondCharge] |
|Local Charge |Local_US[id = id1 and Local_Charge_Name = |
| |Firstlocal] |
| |Local_US[id = id2 and Local_Charge_Name = |
| |Firstlocal] |
| |Local_Peso[id = id1 and Local_Charge_Name = |
| |Secondlocal] |
|Subtotal |US_Subtotal[id = id1] |
| |Peso_Subtotal[id = id1] |
|Discount |US_Discount[id = id1] |
| |Peso_Discount[id = id1] |
|Total Forwarding Cost |((round((((ussobtotal1 - usdiscount1) * |
| |Exchange_Rate) + (psubtotal - pdiscount1)) * |
| |100)) / 100) |
|Brokerage Delivery jCharges |Brokerage_Delivery_Charges[id = id1] |
|Total |Forwarding1 + Broker1 |

C. Assigning of Signatories for the Approval of the Endorsement

Fields involve are the following:

|Field Name |Data Type |Default Value |
|Report_Signatory1 |Text (string) | |
|Report_Signatory2 |Text (string) | |
|Report_Position1 |Text (string) | |
|Report_Position2 |Text (string) | |

Rule 1: • first is less than or equal to 50,000 Actions: • Set a field’s value: A. Report_Signatory1 : “R.A. Cruz” B. Report_Position1: “Procurement Associate” C. Report_Signatory2: “R.B. Marcojos” D. Report_Position2: “Procurement Officer”

Rule 2: • first is greater than 50,000 and first is less than or equal to 200,000 Actions: • Set a field’s value: i. Report_Signatory1 : “R.B. Marcojos” ii. Report_Position1: “Procurement Officer” iii. Report_Signatory2: “A.C. Mendoza” iv. Report_Position2: “MSPD Manager”

Rule 3: • first is greater than 200,000 and first is less than or equal to 5,000,000 Actions: • Set a field’s value: i. Report_Signatory1 : “A.C. Mendoza” ii. Report_Position1: “MSPD Manager” iii. Report_Signatory2: “S.Y. Yu” iv. Report_Position2: “Vice President – Procurement Division”

Rule 4: • first is greater than 5,000,000 and first is less than or equal to 15,000,000 Actions: • Set a field’s value: E. Report_Signatory1 : “S.Y. Yu” F. Report_Position1: “Vice President – Procurement Division” G. Report_Signatory2: “L.B. Nepomuceno” H. Report_Position2: “General Manager”

d. Freight Requirement Report

|Output Type |Formula Used |
|Deadline |my:Freight_Deadline |
|Reference No. |concat(WO_No, "(", @PR_No[@WO_No = WO_No], "/", |
| |@PO_No[@WO_No = WO_No], ")") |
|Cargo Description |@Description_of_Goods[@WO_No = WO_No] |
|Dimension |@Dimension[@WO_No = WO_No] |
|Weight |concat(@Weight_of_Package[@WO_No = WO_No], |
| |@Weight_of_Package_Unit[@WO_No = WO_No]) |
|Mode_of_Packing |@Mode_of_Packing[@WO_No = WO_No] |
|Loading |concat(@Terms[@WO_No = WO_No], "/", @Port[@WO_No = |
| |WO_No]) |
|Discharge |@Discharge[@WO_No = WO_No] |
|Mode_of_Shipment |@Mode_of_Shipment[@WO_No = WO_No] |
|Shipper |@Shipper[@WO_No = WO_No] |
|Shipper Address |@Shipper_Address[@WO_No = WO_No] |
|Shipper Contact Number |@Shipper_TelFax_Number[@WO_No = WO_No] |

e. Abridged Contract Report

|Output Type |Formula Used |
|Deadline |my:Freight_Deadline |
|Reference No. |concat(WO_No, "(", @PR_No[@WO_No = WO_No], "/", @PO_No[@WO_No |
| |= WO_No], ")") |
|Cargo Description |@Description_of_Goods[@WO_No = WO_No] |
|Dimension |@Dimension[@WO_No = WO_No] |
|Weight |concat(@Weight_of_Package[@WO_No = WO_No], |
| |@Weight_of_Package_Unit[@WO_No = WO_No]) |
|Mode_of_Packing |@Mode_of_Packing[@WO_No = WO_No] |
|Loading |concat(@Terms[@WO_No = WO_No], "/", @Port[@WO_No = WO_No]) |
|Discharge |@Discharge[@WO_No = WO_No] |
|Mode_of_Shipment |@Mode_of_Shipment[@WO_No = WO_No] |
|Supplier |@Shipper[@WO_No = WO_No] |
|Supplier Address |@Shipper_Address[@WO_No = WO_No] |
|Supplier Contact Number |@Shipper_TelFax_Number[@WO_No = WO_No] |
|Availability date of item |substring(@Availability_Date_of_Item[@WO_No = WO_No], 1, 10) |
|Duration of Pick up |@Duration_of_Pickup[@WO_No = WO_No] |
|Awaiting Vessel / Plane |@Awaiting_Vessel_Plane[@WO_No = WO_No] |
|Transit time |@Transit_Time[@WO_No = WO_No] |
|ETD |substring(@ETD_Port_of_Origin[@WO_No = WO_No], 1, 10) |
|ETA |substring(@ETA_Manila_Port[@WO_No = WO_No], 1, 10) |
|Delivery Place |@Final_Destination[@WO_No = WO_No] |
|Discharge |@Discharge[@WO_No = WO_No] |
|First Signatory |Signatory_1 (entered in the previous form) |
|Second Signatory |Signatory_2 (entered in the previous form) |

III. Logistics Information Access

a. Data Connections

|Connection Name |Description |
|Main Submit |Submits data to the SharePoint site upon saving of the |
| |form (File name - uniquename) |
|Broker |Receives data from the Broker list at the SharePoint |
| |site |
|Broker Tel No |Receives data from the Broker Tel No list at the |
| |SharePoint site |
|Bank |Receives data from the Bank list at the SharePoint site|
|Premium Rate |Receives data from the Premium Rate list at the |
| |SharePoint site |
|Quantity Unit of Measure |Receives data from the Quantity Unit of Measure list at|
| |the SharePoint site |
|Final Delivery Point |Receives data from the Final Delivery Point list at the|
| |SharePoint site |
|Destination |Receives data from the Destination list at the |
| |SharePoint site |
|FCL |Receives data from the FCL list at the SharePoint site |
|LCL |Receives data from the LCL list at the SharePoint site |
|Weight |Receives data from the Weight list at the SharePoint |
| |site |
|Valuation |Receives data from the Valuation list at the SharePoint|
| |site |
|Type of Shipment |Receives data from the Type of Shipment list at the |
| |SharePoint site |
|Type of Container |Receives data from the Type of Container list at the |
| |SharePoint site |
|Status |Receives data from the Status list at the SharePoint |
| |site |
|Shipper |Receives data from the Shipper list at the SharePoint |
| |site |
|Ship Via |Receives data from the Ship Via list at the SharePoint |
| |site |
|Payment Terms |Receives data from the Payment Terms list at the |
| |SharePoint site |
|Packages |Receives data from the Packages list at the SharePoint |
| |site |
|Option |Receives data from the Option list at the SharePoint |
| |site |
|Local Supplier |Receives data from the Local Supplier list at the |
| |SharePoint site |
|Freight Status |Receives data from the Freight Status list at the |
| |SharePoint site |
|End Users |Receives data from the End Users list at the SharePoint|
| |site |
|Delivery Point |Receives data from the Delivery Point list at the |
| |SharePoint site |
|Currency |Receives data from the Currency at the SharePoint site |
|Request for Freight |Receives data in the Request for Freight form library |
| |at the SharePoint site |
|Logistics Information Access |Receives data in the Logistics Information Access form |
| |library at the SharePoint site |

b. Rules upon opening of the form

Rule 1: • User’s current role is equal to ‘Full Access’ (PETHUB_LIA_ADMINISTRATORS, petnet\etdungo, petnet\jcmarasigan) Actions: • Switch to View ‘Home View’

Rule 2: • User’s current role is equal to ‘End Users’ (PETHUB_LIA_MEMBERS) Actions: • Switch to View ‘End User View’

Rule 3: • User’s current role is not equal to ‘Full Access’ and User’s current role is not equal to ‘End Users’ Actions: • Switch to View to ‘Blank View’ • Show a dialog box message : You are not allowed to view this form

c. Full Access View of the Logistics Information Access Form (View 1)

Every time, shipping documents of the purchase orders are received, any of the pethub_lia_administrators members have to fill up the following fields of the form.

|Field Name |Data Type |Default Value |
|PO_No |Text (string) | |
|JO_No |Text (string) | |
|Delivery_Date |Date (date) | |
|Status (Drop Down) |Text (string) |(Status - Secondary Data Source) @Status |
|Status_Date |Date (date) |today () |
|Remarks |Text (string) | |
|Supplier_Name |Text (string) | |
|Local_Supplier_Address |Text (string) | |
|PO_Date |Date (date) | |
|Cost_Center |Text (string) | |
|FX_Rate |Text (string) | |
|Requisition_No |Text (string) | |
|Item_Description |Text (string) | |
|Delivery_Point_Dropdown (Drop Down_) |Text (string) |(Delivery Point - Secondary Data Source) |
| | |@Delivery Point |
|Delivery_Point_Textbox |Text (string) | |
|Delivery_Point |Text (string) | |
|Final_Delivery_Point_Dropdown (Drop Down) |Text (string) |(Final Delivery Point - Secondary Data Source) |
| | |@Final Delivery Point |
|Final_Delivery_Point_Textbox |Text (string) | |
|Final_Delivery_Point |Text (string) | |
|Comm_Code |Text (string) | |
|Payment_Terms |Text (string) | |
|LC_No |Text (string) | |
|Bank (Drop Down) |Text (string) |(Bank - Secondary Data Source) @Bank |
|PO_Value_Currency_Dropdown (Drop Down) |Text (string) |(Currency - Secondary Data Source) @Currency |
|PO_Value1 |Decimal (double) | |
|PO_Value_Currency_Textbox |Text (string) | |
|PO_Value2 |Decimal (double) | |
|PO_Value_Currency |Text (string) | |
|PO_Value |Decimal (double) | |
|Equivalent_Value_In_Peso |Decimal (double) |round(PO_Value * FX_Rate * 100) / 100 |
|Origin |Text (string) | |
|Valuation (Drop Down) |Text (string) |(Valuation - Secondary Data Source) @Valuation |
|End_User_Dropdown (Drop Down) |Text (string) |(End User - Secondary Data Source) @End User |
|End_User_Textbox |Text (string) | |
|End_User |Text (string) | |
|Buyer |Text (string) | |
|Broker (Drop Down) |Text (string) |(Broker - Secondary Data Source) @Broker |
|Broker_Tel_No (Drop Down) |Text (string) |(Broker Tel No - Secondary Data Source) @Broker|
| | |Tel No |
|TA |Text (string) | |
|Date_Advance_Shipping_Docs_Recd |Date (date) | |
|Date_TelexFax_Message_Recd |Date (date) | |
|BLAWB_Date |Date (date) | |
|Docs_Recd |Date (date) | |
|BLAWB_No |Text (string) | |
|Carrier |Text (string) | |
|BLAWB_House_No |Text (string) | |
|Voyage_Flight_No |Text (string) | |
|Nt_Weight |Decimal (double) | |
|Nt_Wt_Unit (Drop Down) |Text (string) |(Weight - Secondary Data Source) @Weight |
|Mode_of_Delivery (Containerized) |Text (string) |Containerized |
|Mode_of_Delivery (Loose Cargo) |Text (string) |Loose Cargo |
|FCL_truck (Drop Down) |Text (string) |(FCL - Secondary Data Source) @FCL |
|LCL_truck (Drop Down) |Text (string) |(LCL - Secondary Data Source) @LCL |
|Groos_Wt |Decimal (double) | |
|Gross_Wt_Unit (Drop Down) |Text (string) |(Weight - Secondary Data Source) @Weight |
|Ship_Via |Text (string) | |
|Quantity |Decimal (double) | |
|Quantity_Unit (Drop Down) |Text (string) |(Quantity Unit of Measure - Secondary Data |
| | |Source) @ Quantity Unit of Measure |
|Shipper |Text (string) | |
|Shipper_Address |Text (string) | |
|No_of_Packages1 |Decimal (double) | |
|Packages_Dropdown (Drop Down) |Text (string) |(Packages - Secondary Data Source) @Packages |
|No_of_Packages2 |Decimal (double) | |
|Packages_Textbox |Text (string) | |
|No_of_Packages |Decimal (double) | |
|Packages |Text (string) | |
|Density |Decimal (double) | |
|Advice_Item_Description |Text (string) | |
|Invoice_No |Text (string) | |
|Invoice_Value_Currency_Dropdown (Drop Down) |Text (string) |(Currency - Secondary Data Source) @Currency |
|Invoice_Value |Decimal (double) | |
|Invoice_Value_Currency_Textbox |Text (string) | |
|Invoice_Value2 |Decimal (double) | |
|Container_No |Text (string) | |
|Peso_Value |Decimal (double) | |
|TH |Text (string) | |
|Tarriff_Rate |Text (string) | |
|ETD_EXW |Date (date) | |
|ETA_Manila_Port |Date (date) | |
|ADR |Date (date) | |
|ETD_Port_of_Origin |Date (date) | |
|ETA_End_User |Date (date) | |
|ADA |Date (date) | |
|Freight_Amount_Currency_Dropdown (Drop Down) |Text (string) |(Currency - Secondary Data Source) @Currency |
|Freight_Amount1 |Decimal (double) | |
|Freight_Amount_Currency_Textbox |Text (string) | |
|Date_Declared |Date (date) | |
|PR |Decimal (double) | |
|ER |Decimal (double) | |

Functions Involve in the Full Access View of the Logistics Information Access Form

A. Checking if the P.O No exists in the Request for Freight Library and Logistics Information Access Library

Purchase orders that have FOB Port of Exit or Ex Works as terms are first entered in the Request for Freight form. Upon completion of this form, it will now then be inputted in the LIA form. Thus, there are some information entered in the Request for Freight Library regarding the purchase order that should be retrieved in the LIA form upon entry of that purchase order so that multiple inputs of data can be avoided. This goes the same whenever there are purchase orders which have multiple job orders. Thus, all the purchase order information for all the job orders should majority all be the same though there are still some info that may differ such as item description and delivery point.

Hidden fields created in order to perform the function include the following:

|Field Name |Data Type |Default Value |
|PO_checkrequest |Text (string) | |
|PO_checklia |Text (string) | |
|counting |Whole Number (integer) | |

Rule 1: This checks whether the purchase order entered exists in the Request for Freight Library and Logistics Information Access Library. • PO_No is not blank Actions: • Set a field’s value i. PO_checkrequest: contains(@PO_No[. = PO_No], .) ii. PO_checklia: contains(@PO_No[. = PO_No], .)

Rule 2: Since there are purchase orders which have multiple job orders, this rule simply checks if it’s the first job order entered for a particular purchase order. If so, then a field is set to 1. • PO_checklia = “false” Actions: • Set a field’s value: i. Counting : 1 Rule 3: This checks whether the purchase order entered already exists and sets a field in the form with the value of the maximum number of job ordered entered for that PO plus one. • PO_checklia = “true” Actions: • Set a field’s value: i. counting: max(@counting[PO_No = @PO_No]) + 1

Rule 4: If the purchase order entered already exists in the Request for Freight Library but not yet in the Logistics Information Access library, some of the fields (Shopping Cart, Item Description, Supplier Name, Supplier Address, Shipper, Shipper Address, Origin, Mode of Delivery, Valuation, Gross Weight and Unit, No of Packages, ADA, ADR, Delivery Date, Mode of Shipment, - Duties and Taxes Form: Processing Time, Delivery Charges, Total DT, Freight Cost) in the form will be set with the value similar to that entered in the Request for Freight form. • PO_checkrequest is equal to “true” and PO_checklia is equal to “false” Actions: The formulas below are used in the sample fields in order to perform the function. • Set a field’s value : i. Requisition_No: @PR_No[PO_No = @PO_No and round(@counting) = counting] ii. Item_Description: @Description_of_Goods[PO_No = @PO_No and counting = round(@counting)] iii. Supplier_Name: @Local_Supplier[PO_No = @PO_No and counting = round(@counting)]

Rule 5: If the purchase order entered already exists in the Request for Freight Library as well as in the Logistics Information Access library, some of the fields (Requisition No, FX Rate, Supplier Name, PO Date, Cost Center, Item Description, Promised Shipping Date, Payment Terms, Comm Code, Origin, Valuation, Equivalent Value in Peso, Buyer, Final Delivery Point, PO Value, End User, Delivery Point, LC No, Bank, Supplier Address, Shipper, Shipper Address, Mode of Delivery, Gross Weight and Unit, Packages, ADA, ADR, Delivery Date, Mode of Shipment, - Duties and Taxes Form: Processing Time, Delivery Charges, Total DT, Freight Cost) in the form will be set with the value similar to that entered in the Request for Freight form and LIA form. • PO_checkrequest is equal to “true” and PO_checklia is equal to “true” Actions: The formulas below are used in the sample fields in order to perform the function. • Set a field’s value: i. Requisition No: @Requisition_No[PO_No = @PO_No and round(@counting) = 1] ii. FX Rate: @FX_Rate[PO_No = @PO_No and round(@counting) = 1] iii. Supplier Name: @Local_Supplier[PO_No = @PO_No and counting = round(@counting)]

Rule 6: If the purchase order entered does not exist in the Request for Freight Library yet it exists in the Logistics Information Access library, some of the fields (Requisition No, FX Rate, Supplier Name, PO Date, Cost Center, Item Description, Promised Shipping Date, Payment Terms, Comm Code, Origin, Valuation, Equivalent Value in Peso, Buyer, Final Delivery Point, PO Value, End User, Delivery Point, LC No, Bank, Supplier Address) in the form will be set with the value similar to the purchase order entered previously in the LIA form. • PO_checkrequest is equal to “false” and PO_checklia is equal to “true” Actions: The formulas below are used in the sample fields in order to perform the function. • Set a field’s value: i. Final_Delivery_Point: @Final_Delivery_Point[PO_No = @PO_No] ii. PO_Value: @PO_Value[PO_No = @PO_No] iii. End User: @End_User[PO_No = @PO_No]

B. Computation of the Insurance Cost

This will be used in the Summary of Shipments, Insurance Marine Debit Report and Insurance Certification.

Hidden fields created in order to perform the function include the following:

|Field Name |Data Type |Default Value |
|Premium |Decimal (double) |round((Invoice_Value * ER * 1.40 * PR) * 100) / |
| | |100 |
|Stamps |Decimal (double) | |
|Insurance_VAT |Decimal (double) | |
|LTAX |Decimal (double) | |
|IC |Decimal (double) | |
|Amount_Insured |Decimal (double) |round(Invoice_Value * ER * 1.40 * 100) / 100 |

Rule: • Premium is not blank Actions: • Set a field’s value: i. Stamps: round(. * 0.125 * 100) / 100 ii. Insurance_VAT: round(. * 0.12 * 100) / 100 iii. LTAX : round(. * 0.002 * 100) / 100 iv. IC: round((. + Stamps + Insurance_VAT + LTAX) * 100) / 100

After completing all the information in the LIA form, it will now then be sent to the specified end user. Thus, the end user will be able to view all the entered information in the LIA form excluding the Insurance Record but inclusive of the Duties and Taxes. Their access is limited to the view only feature which means that they can not add, edit or delete any information.

b. Duties and Taxes View of the Form

Once the purchase order and job order information are complete, the next thing to do is to input the duties and taxes information in accordance to P.O and J.O. specified. Thus, the following fields should be filled out.

|Field Name |Data Type |Default Value |
|PO_No |Text (string) | |
|JO_No |Text (string) | |
|Item_Description |Text (string) | |
|Requisition_No |Text (string) | |
|Entry_No |Text (string) | |
|BCOR_No |Text (string) | |
|IERD_No |Text (string) | |
|Shipper |Text (string) | |
|Shipper_Address |Text (string) | |
|Supplier_Name |Text (string) | |
|Local_Supplier_Address |Text (string) | |
|BLAWB_No |Text (string) | |
|Broker |Text (string) | |
|Processing_Time |Text (string) | |
|ADA |Date (date) | |
|ADR |Date (date) | |
|PO_Value_Currency_Dropdown (Drop Down) |Text (string) |(Currency - Secondary Data Source) @Currency |
|PO_Value1 |Decimal (double) | |
|PO_Value_Currency_Textbox |Text (string) | |
|PO_Value2 |Decimal (double) | |
|Invoice_Value_Currency_Dropdown (Drop Down) |Text (string) |(Currency - Secondary Data Source) @Currency |
|Invoice_Value1 |Decimal (double) | |
|Invoice_Value_Currency_Textbox |Text (string) | |
|Invoice_Value2 |Decimal (double) | |
|ER |Decimal (double) | |
|TH |Text (string) | |
|Tarriff_Rate |Text (string) | |
|Duties |Decimal (double) | |
|VAT |Decimal (double) | |
|Excise |Decimal (double) | |
|Total_DT |Decimal (double) | |
|IPF |Decimal (double) | |
|BF |Decimal (double) | |
|DC |Decimal (double) | |
|OC |Decimal (double) | |
|FC |Decimal (double) | |
|Premium |Decimal (double) |round((Invoice_Value * ER * 1.40 * PR) * 100) / |
| | |100 |
|IC |Decimal (double) | |
|Total_OC |Decimal (double) |IPF + BF + DC + OC + FC + IC |
|Total_DT_and_OC |Decimal (double) |Total_OC + Total_DT |
|DV_in_Peso |Decimal (double) | |
|TC_Before_VAT |Decimal (double) | |
|TC_After_VAT |Decimal (double) | |
|Nt_Weight |Decimal (double) | |
|Nt_Wt_Unit (Drop Down) |Text (string) |(Weight - Secondary Data Source) @Weight |
|Gross_Wt |Decimal (double) | |
|Gross_Wt_Unit (Drop Down) |Text (string) |(Weight - Secondary Data Source) @Weight |
|Final_Delivery_Point |Text (string) | |
|No_of_Packages |Decimal (double) | |
|Packages |Text (string) | |
|Mode_of_Delivery (Containerized) |Text (string) |Containerized |
|Mode_of_Delivery (Loose Cargo) |Text (string) |Loose Cargo |
|FCL_truck |Text (string) | |
|LCL_truck |Text (string) | |
|Quantity |Decimal (double) | |
|Quantity_Unit |Text (string) | |

After completing the form, it will now be saved in the Logistics Information Access Library updating the information for the particular job order.

IV. Reports

a. Data Connections

|Connection Name |Description |
|Request for Freight |Receives data in the Request for Freight form library |
| |at the SharePoint site |
|Request for Freight2 |Receives data in the Request for Freight form library |
| |at the SharePoint site |
|Logistics Information Access |Receives data in the Logistics Information Access form |
| |library at the SharePoint site |
|Logistics Information Access2 |Receives data in the Logistics Information Access form |
| |library at the SharePoint site |

b. Rules upon opening of the form

Rule 1: • User’s current role is not equal to ‘Full Access’ Actions: • Switch to View to ‘Blank View’ • Show a dialog box message : You are not allowed to view this form

Upon opening of the form, the user has to choose which type of report he needs to view and print. The reports include the following:

c. Application for ATRIG

Before generating the actual application for ATRIG, the following should be first filled out.

|Field Name |Data Type |Default Value |
|JO_Num |Text (string) | |
|Declarant |Text (string) | |
|Customs_Broker |Text (string) | |
|Broker_Address |Text (string) | |
|Broker_Tel_No |Text (string) | |
|Broker_Tin_No |Text (string) | |
|Residence_Certificate_No |Text (string) | |
|Place_of_Issue |Text (string) | |
|Date_of_Issue |Date (date) | |
|Initial |Text (string) | |

The outputs for the report should include the following:

|Output Type |Formula Used |
|Broker |my:Customs_Broker |
|Tin No |my:Broker_Tin_No |
|Address |my:Broker_Address |
|Tel No |my:Broker_Tel_No |
|Vessel |@Voyage_Flight_No[JO_Num = @JO_No] |
|ETA |substring(@ETA_Manila_Port[JO_Num = @JO_No], 1, |
| |10) |
|B/L No |@BLAWB_No[JO_Num = @JO_No] |
|B/L Date |substring(@BLAWB_Date[JO_Num = @JO_No], 1, 10) |
|Description of Commodities |@Item_Description[JO_Num = @JO_No] |
|Quantity |round(@No_of_Packages[JO_Num = @JO_No] * 100) / |
| |100 |
| |@Packages[JO_Num = @JO_No] |
|Dollar Value |@Invoice_Value_Currency[JO_Num = @JO_No] |
| |round(@Invoice_Value[JO_Num = @JO_No] * 100) / |
| |100 |
|Initial |my:Initial |
|Broker |@Broker[JO_Num = @JO_No] |
|Declarant |my:Declarant |

d. ATRIG

Before generating the actual ATRIG, the following should be first filled out.

|Field Name |Data Type |Default Value |
|JO_Num |Text (string) | |
|Attention |Text (string) | |
|Declarant |Text (string) | |
|Initial |Text (string) | |

The outputs for the report should include the following:

|Output Type |Formula Used |
|Attention |translate(my:Attention, ",", ".") |
| |concat(substring-before(Attention, ","), |
| |substring-after(Attention, "."), ",") |
|Number of Packages |round(@No_of_Packages[JO_Num = @JO_No] * 100) / 100 |
| |@Packages[JO_Num = @JO_No] |
|Description |@Item_Description[JO_Num = @JO_No] |
|Value |@Invoice_Value_Currency[JO_Num = @JO_No] |
| |round(@Invoice_Value[JO_Num = @JO_No] * 100) / 100 |
|Density |concat(round(@Density[JO_Num = @JO_No] * 100000) / 100000, " ",|
| |"Density =") |
|Liters |concat(round(((round(@No_of_Packages[JO_Num = @JO_No] * 100) / |
| |100) / (round(@Density[JO_Num = @JO_No] * 100000) / 100000)) * |
| |100) / 100, " ", "Liters") |
|Initial |my:Initial |
|Broker |@Broker[JO_Num = @JO_No] |
|Declarant |my:Declarant |

e. Authorization Letter

Before generating the actual Authorization Letter, the following should be first filled out.

|Field Name |Data Type |Default Value |
|JO_Num |Text (string) | |
|Customs_Broker |Text (string) | |
|Declarant |Text (string) | |
|Initial |Text (string) | |

The outputs for the report should include the following:

|Output Type |Formula Used |
|Header |@Carrier[JO_Num = @JO_No] |
|Customs Broker |my:Customs_Broker |
|Voyage / Flight No |@Voyage_Flight_No[JO_Num = @JO_No] |
|B/L No |@BLAWB_No[JO_Num = @JO_No] |
|Description |@Item_Description[JO_Num = @JO_No] |
|Declarant |my:Declarant |

f. Insurance Certification

Before generating the actual Insurance Certification, the following should be first filled out.

|Field Name |Data Type |Default Value |
|JO_Num |Text (string) | |
|Signatory |Text (string) | |

The outputs for the report should include the following:

|Output Type |Formula Used |
|Policy No |MOP Dry Cargo No. MOPRODC |
|Period |concat("January 1,", substring(today(), 1, 4), " |
| |", "to", " ", concat("January 1, ", |
| |(substring(today(), 1, 4)) + 1)) |
|Amount Insured |round(@Amount_Insured[JO_Num = @JO_No] * 100) / |
| |100 |
|Premium Rate |@PR[JO_Num = @JO_No] |
|Premium Value |round(@Premium[JO_Num = @JO_No] * 100) / 100 |
|JO Number |my:JO_Num |
|Description |@Item_Description[JO_Num = @JO_No] |
|B/L No |@BLAWB_No[JO_Num = @JO_No] |
|Signatory |my:Signatory |

g. Insurance Declaration Marketing

Before generating the actual Insurance Certification, the following should be first filled out. This should include all the job orders that starts with “P”

|Field Name |Data Type |Default Value |
|Date_declared |Date (date) | |

The outputs for the report should include the following:

|Output Type |Formula Used |
|Reqn No |@Requisition_No |
|PO No |@PO_No |
|Cost Center |@Cost_center |
|ETD |@ETD_Port_of_Origin |
|ETA |@ETA_Manila_Port |
|Carrier |@Carrier |
|Origin |@Origin |
|Destination |@Delivery_Point |
|Item |@Item_Description |
|Value (PHP) |@Amount_Insured |
|Premium Rate |@PR |
|Premium (PHP) |@Premium |
|Total Value |round(sum(@Amount_Insured[Date_Declared = |
| |substring(@Date_Declared, 1, 10) and substring(@JO_No, 1, |
| |1) = "P"]) * 100) / 100 |
|Total Premium |round(sum(@Premium[Date_Declared = |
| |substring(@Date_Declared, 1, 10) and substring(@JO_No, 1, |
| |1) = "P"]) * 100) / 100 |

h. Insurance Declaration Refinery

Before generating the actual Insurance Certification, the following should be first filled out. This should include all the job orders that have a delivery point of Bataan.

|Field Name |Data Type |Default Value |
|Date_declared |Date (date) | |

The outputs for the report should include the following:

|Output Type |Formula Used |
|Reqn No |@Requisition_No |
|PO No |@PO_No |
|Cost Center |@Cost_center |
|ETD |@ETD_Port_of_Origin |
|ETA |@ETA_Manila_Port |
|Carrier |@Carrier |
|Origin |@Origin |
|Destination |@Delivery_Point |
|Item |@Item_Description |
|Value (PHP) |@Amount_Insured |
|Premium Rate |@PR |
|Premium (PHP) |@Premium |
|Total Value |round(sum(@Amount_Insured[Date_Declared = |
| |substring(@Date_Declared, 1, 10) and substring(@JO_No, 1, |
| |1) = "P"]) * 100) / 100 |
|Total Premium |round(sum(@Premium[Date_Declared = |
| |substring(@Date_Declared, 1, 10) and substring(@JO_No, 1, |
| |1) = "P"]) * 100) / 100 |

i. Insurance Marine Debit

Before generating the actual Insurance Marine Debit, the following should be first filled out.

|Field Name |Data Type |Default Value |
|JO_Num |Text (string) | |
|Signatory |Text (string) | |

The outputs for the report should include the following:

|Output Type |Formula Used |
|Shipped Via |@Voyage_Flight_No[JO_Num = @JO_No] |
|B/L No |@BLAWB_No[JO_Num = @JO_No] |
|Date |substring(@BLAWB_Date[JO_Num = @JO_No], 1, 10) |
|Value |@Invoice_Value_Currency[JO_Num = @JO_No] |
|Exchange Rate |round(@ER[JO_Num = @JO_No] * 10000) / 10000 |
|Amount Insured |round(@Amount_Insured[JO_Num = @JO_No] * 100) / |
| |100 |
|Covering |@Item_Description[JO_Num = @JO_No] |
|Origin |@Origin[JO_Num = @JO_No] |
|Destination |@Delivery_Point[JO_Num = @JO_No] |
|Premium |round(@Premium[JO_Num = @JO_No] * 100) / 100 |
|Stamps |round(@Stamps[JO_Num = @JO_No] * 100) / 100 |
|VAT |round(@Insurance_VAT[JO_Num = @JO_No] * 100) / |
| |100 |
|LTAX |round(@LTAX[JO_Num = @JO_No] * 100) / 100 |
|Total |round(@IC[JO_Num = @JO_No] * 100) / 100 |

j. Job Order Report

Before generating the actual Job Order Report, the following should be first filled out.

|Field Name |Data Type |Default Value |
|JO_Num |Text (string) | |
|Attention |Text (string) | |
|Declarant |Text (string) | |
|Initial |Text (string) | |

The outputs for the report should include the following:

|Output Type |Formula Used |
|Job Order No |my:JO_Num |
|Reqn No |@Requisition_No[JO_Num = @JO_No] |
|PO_No |@PO_No[JO_Num = @JO_No] |
|Container No |@Container_No[JO_Num = @JO_No] |
|Carier / Voy No |@Voyage_Flight_No[JO_Num = @JO_No] |
|BL or AWB No |@BLAWB_No[JO_Num = @JO_No] |
|Description |@Item_Description[JO_Num = @JO_No] |
|Incoterms |@Valuation[JO_Num = @JO_No] |
|Payment Terms |@Payment_Terms[JO_Num = @JO_No] |
|Deliver to |@Delivery_Point[JO_Num = @JO_No] |
|Attention |my:Attention |
|Final Destination |@Final_Delivery_Point[JO_Num = @JO_No] |
|Initial |my:Initial |
|Declarant |my:Declarant |

The reports mentioned above can be generated and printed.